SQLSTATE[42000]: Syntax error or access violation: 1055 Expression #1

[eiskaltereistee]

Moin,

bin grade dabei, ein totes open source Projekt wieder aufzubauen.

Aktuell kämpfe ich mit einem Problem:

An exception has been thrown during the rendering of a template ("SQLSTATE[42000]: Syntax error or access violation: 1055 Expression # 1 of ORDER BY clause is not in GROUP BY clause and contains nonaggregated column 'circlebilling.p.priority' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by") in "mod_order_index.phtml" at line 20.

Folgender Query sorgt für den Fehler:

public function getProductCategorySearchQuery($data)
{
    $sql = "SELECT m.*
            FROM product_category as m
              LEFT JOIN product p on p.product_category_id = m.id
            WHERE p.status = 'enabled'
              AND p.hidden = 0
            GROUP BY p.product_category_id
            ORDER BY p.priority ASC
    ";

    $params = array();

    return array($sql, $params);
} 

Was genau ist die richtige Lösung für das Problem? Bin ich wirklich gezwungen meinen SQL Mode in der Datenbank zu ändern( https://mariadb.com/kb/en/library/sql-mode/)? Oder gibt es eine Möglichkeit den Query anzupassen?

Ich bin gespannt was ihr für Lösungsansätze habt. Vielen Dank für jede Bemühung.

[akretschmer]

Die Lösung wäre es richtig zu machen. Wenn Du gruppierst, dann ALLE Spalten entweder gruppieren oder aggregieren.

[akretschmer]

Die Lösung wäre es richtig zu machen. Wenn Du gruppierst, dann ALLE Spalten entweder gruppieren oder aggregieren.

sorry für doppelte Antwort, aber beim ersten Versuch kamen Fehlermeldungen...

[eiskaltereistee]

akretschmer vielen Dank für deine Antwort! Ich habe versucht die benötige spalte mit zu gruppieren:

SELECT m.*
                FROM product_category as m
                  LEFT JOIN product p on p.product_category_id = m.id
                WHERE p.status = 'enabled'
                  AND p.hidden = 0
                GROUP BY p.product_category_id, p.priority
                ORDER BY p.priority ASC 

Hier werden allerdings die Kategorien so oft zurück gegeben, wie Artikel der Kategorie zugeordnet sind. Hast du mal ein Beispiel für beides? Sowohl für das Gruppieren aller Spalten als auch mittels aggregieren?

[akretschmer]

ich weiß nicht was Du an Tabellen und Spalten hast und was rauskommen soll. Vielleicht hilft Dir aber die Syntaxdarstellung eines SELECT-Befehls:

Command:     SELECT
Description: retrieve rows from a table or view
Syntax:
[ WITH [ RECURSIVE ] with_query [, ...] ]
SELECT [ ALL | DISTINCT [ ON ( expression [, ...] ) ] ]
    [ * | expression [ [ AS ] output_name ] [, ...] ]
    [ FROM from_item [, ...] ]
    [ WHERE condition ]
    [ GROUP BY grouping_element [, ...] ]
    [ HAVING condition [, ...] ]
    [ WINDOW window_name AS ( window_definition ) [, ...] ]
    [ { UNION | INTERSECT | EXCEPT } [ ALL | DISTINCT ] select ]
    [ ORDER BY expression [ ASC | DESC | USING operator ] [ NULLS { FIRST | LAST } ] [, ...] ]
    [ LIMIT { count | ALL } ]
    [ OFFSET start [ ROW | ROWS ] ]
    [ FETCH { FIRST | NEXT } [ count ] { ROW | ROWS } ONLY ]
    [ FOR { UPDATE | NO KEY UPDATE | SHARE | KEY SHARE } [ OF table_name [, ...] ] [ NOWAIT | SKIP LOCKED ] [...] ]

where from_item can be one of:

    [ ONLY ] table_name [ * ] [ [ AS ] alias [ ( column_alias [, ...] ) ] ]
                [ TABLESAMPLE sampling_method ( argument [, ...] ) [ REPEATABLE ( seed ) ] ]
    [ LATERAL ] ( select ) [ AS ] alias [ ( column_alias [, ...] ) ]
    with_query_name [ [ AS ] alias [ ( column_alias [, ...] ) ] ]
    [ LATERAL ] function_name ( [ argument [, ...] ] )
                [ WITH ORDINALITY ] [ [ AS ] alias [ ( column_alias [, ...] ) ] ]
    [ LATERAL ] function_name ( [ argument [, ...] ] ) [ AS ] alias ( column_definition [, ...] )
    [ LATERAL ] function_name ( [ argument [, ...] ] ) AS ( column_definition [, ...] )
    [ LATERAL ] ROWS FROM( function_name ( [ argument [, ...] ] ) [ AS ( column_definition [, ...] ) ] [, ...] )
                [ WITH ORDINALITY ] [ [ AS ] alias [ ( column_alias [, ...] ) ] ]
    from_item [ NATURAL ] join_type from_item [ ON join_condition | USING ( join_column [, ...] ) ]

and grouping_element can be one of:

    ( )
    expression
    ( expression [, ...] )
    ROLLUP ( { expression | ( expression [, ...] ) } [, ...] )
    CUBE ( { expression | ( expression [, ...] ) } [, ...] )
    GROUPING SETS ( grouping_element [, ...] )

and with_query is:

    with_query_name [ ( column_name [, ...] ) ] AS ( select | values | insert | update | delete )

TABLE [ ONLY ] table_name [ * ]

postgres=#

Die Teile, die MySQL nicht kann, einfach überlesen, da wird das schon deutlich kürzer Eventuell wäre ein DISTINCT ON (...) für Dich auch sinnvoll - ist aber eine nicht portable PostgreSQL-Erweiterung, kann ich also weglassen zu zeigen.

[Perry Staltic]

Ich habe versucht die benötige spalte mit zu gruppieren:
...

Du gruppierst nach Feldern aus P, selektierst aber Felder aus M!
Wo liegt der Sinn darin?

Wenn es Sinn machen sollte, dann sehr selten und wir sind da wahrscheinlich mal wieder im Weltall auf einem Lichtjahre entfernten erdähnlichen Planeten.

Wann wird überhaupt gruppiert?
Wenn die Select Clause Aggregatfunktionen enthält. Tut sie bei Dir nicht.

Wie kann man Dir helfen?
Wenn Du deine Tabellendefinitionen lieferst, bei Select * Statements den * durch die wirklich benötigten Felder ersetzt und nach Bedarf auch Beispieldaten lieferst. Bspw. in Soll/ Ist Form oder so. "Oder so" bedeutet allerdings nicht, Bilder oder Videos zu posten, sondern Text aus dem SQL Tool Deiner Wahl.

ot
ich erhalte wiederholt Serverfehler beim Antworten in FF (offenbar wir akretschmer)
/ot

[eiskaltereistee]

Moin,

ich danke euch beiden schonmal.

Grob worum es geht: Ich habe zwei Tabellen. Einmal die product_category (Kategorien) und einmal die product (Produkte). Jetzt möchte ich mir alle Kategorien anzeigen lassen, wo Aktive Produkte (status = enabled und hidden = 0) drin sind. Die Kategorien sollten nach dem Sortierungsfeld "priority" auf dem Produkt sortiert werden.

Ich bin wirklich gespannt, wie ihr da ran geht!

Den Server Fehler erhalte ich auch... klappt ja trotzdem mit dem posten!

[erc]

Sortier mal die Kategorien:

Kategorie 1:
Produkt A mit priority 100
Produkt B mit priority 1

Kategorie 2:
Produkt C mit priority 99
Produkt D mit priority 89
Produkt E mit priority 88

Verstehst du die zwicklage der Datenbank? Du musst deine Anforderungen klar definieren,.ganz besonders wenn die Sprache SQL heißt. Computer sind in der Regel schlecht beim Raten.

[eiskaltereistee]

Mh ja, grob kann ich mir vorstellen, was da schief läuft..
Wäre es denn möglich, den Query sauber zu bauen, sodass ich das gewünschte Ergebnis erhalte?
Die Alternative (Finde ich sowieso sauberer) wäre, dass ich ein Sort-Feld zu den Kategorien hinzufüge. Kann ja sein, dass der Benutzer im Backend die Produkte mal anders anordnen möchte als die Kategorien oder was meint ihr?
Aber selbst wenn ich da anders umsetzte würde mich trotzdem die Lösung des Queries interessieren.

[Perry Staltic]

Sortier mal die Kategorien:
..
Verstehst du die zwicklage der Datenbank?

Also mit der Anforderung und der Datenlage ist klar, dass es nicht gehen kann.
Wie willst Du nach einem Wert sortieren, der je Kategorie beliebig oft und natürlich verschieden vorkommen kann?

Du oder Dein Auftraggeber muss überlegen, nach welcher Priorität sortiert werden soll. Beispiel

SELECT m.id, m.title, m.description, m.icon_url, m.created_at, m.updated_at,
       --p.priority,                        -- das würde die Group by Anweisung brechen
       min(p.priority) as MinPrio,          -- such Dir was aus
       avg(p.priority) as AvgPrio,          -- such Dir was aus
       max(p.priority) as MaxPrio,          -- such Dir was aus
       count(*)        as AnzahlProdukte,   -- verschaff Dir Überblick 
  FROM product_category as m
  LEFT JOIN product p
    ON p.product_category_id = m.id
 WHERE p.status = 'enabled'
   AND p.hidden = 0
 GROUP BY m.id, m.title, m.description, m.icon_url, m.created_at, m.updated_at
 ORDER BY 7 ASC                             -- MinPrio augesucht