SQL Query mit mehreren Customfields als Sortierung (für das Order By)

  • Hallo zusammen,


    ich habe unsere Vereinsseite erweitert mit einer Fülle Customfields zum Thema Literaturinformationen. Nun habe ich mit ein Override für eine Kategorieansicht erstellt, welche das Sortieren nach Customfield-Inhalten ermöglicht.


    Nun wird zum Beispiel bei der Sortierung der Buchtitel bzw. Veröffentlichungen nach Autor richtig sortiert mit diesem Code


    Code
    $query = "SELECT content.*, fieldvalues.* FROM #__content AS content, #__fields_values AS fieldvalues WHERE content.catid=" . $catid . " AND content.id = fieldvalues.item_id AND fieldvalues.field_id=418 AND fieldvalues.value LIKE '%" . $sorttype . "%' ORDER BY fieldvalues.value ASC;";


    Ich möchte aber nun, dass wenn der Autor mehr als ein Werk veröffentlicht hat, diese zusätzlich nach dem Erscheinungsjahr sortiert werden, welches in der fieldvalues.field_id=131 abgelegt ist.


    Mir ist klar, dass so etwas möglich ist aber ich finde keine Ansätze das umzusetzen. Vielleicht könnt Ihr helfen oder mir eine Schubs in die richtige Richtung (Tutorial, Example .. etc.) geben.


    Bleibt gesund und vielen Dank!

  • Ich meine du kannst das anhängen:

    Code
    $query = "SELECT content.*, fieldvalues.* FROM #__content AS content, #__fields_values AS fieldvalues WHERE content.catid=" . $catid . " AND content.id = fieldvalues.item_id AND fieldvalues.field_id=418 AND fieldvalues.value LIKE '%" . $sorttype . "%' ORDER BY fieldvalues.value ASC, fieldvalues.field_id=131 ASC;";
  • Sorry, da hatte ich zu schnell geschaut.

    Sort-by sortiert zunächst nach der ersten Spalte. Wenn in dieser mehrere gleiche Werte vorliegen, wird nach einer zweite Spalte sortiert ( MySQL :: MySQL 8.0 Reference Manual :: 8.2.1.16 ORDER BY Optimization ).


    In deinem Fall liegen aber keine zwei Spalten vor und Christianes Tipp ist der richtige.


    Ich mache das auch immer so, dass ich die Query erst in reinem SQL aufbaue:

    MySQL :: MySQL 8.0 Reference Manual :: 13.2.10.2 JOIN Clause. Wenn alles klappt wandele ich es in die optimierte Joomla-Variante um.

  • Das Prinzip:


    Du magst com_content anzeigen:


    SQL
    SELECT `j3dev_content`.`title`  FROM `j3dev_content`


    Nun nimmst du die erste Spalte hinzu:



    SQL
    SELECT `a`.`value`, `a`.`field_id`, `j3dev_content`.`title` FROM `j3dev_content`
    LEFT JOIN
    (`j3dev_fields_values` as a)
    ON (`a`.`item_id` = `j3dev_content`.`id` and `a`.`field_id` = 2)


    Die zweite Spalte kommt dann analog hinzu:



    SQL
    SELECT `a`.`value`, `a`.`field_id`, `b`.`value`, `b`.`field_id`, `j3dev_content`.`title`  FROM `j3dev_content`
    LEFT JOIN
    (`j3dev_fields_values` as a)
    ON (`a`.`item_id` = `j3dev_content`.`id` and `a`.`field_id` = 2)
    LEFT JOIN
    (`j3dev_fields_values` as b)
    ON (`b`.`item_id` = `j3dev_content`.`id` and `b`.`field_id` = 3)



    Nun kannst du sortieren:



    SQL
    SELECT `a`.`value`, `a`.`field_id`, `b`.`value`, `b`.`field_id`, `j3dev_content`.`title`  FROM `j3dev_content` 
     LEFT JOIN
     (`j3dev_fields_values` as a) 
     ON (`a`.`item_id` = `j3dev_content`.`id` and `a`.`field_id` = 2)
     LEFT JOIN
     (`j3dev_fields_values` as b) 
     ON (`b`.`item_id` = `j3dev_content`.`id` and `b`.`field_id` = 3)
     order by `a`.`value`, `b`.`value`


    Hilfreich ist vielleicht auch noch: https://docs.joomla.org/Selecting_data_using_JDatabase Aber wie schon geschrieben, ich finde es zu Beginn immer gut, die Query in purem SQL aufzubauen und dann in Joomla umzuwandeln. Andernfalls kann es passieren, das man den Fehler in der Query sucht, dabei war ein Joomla-Befehl der Schuldige.

  • Also wenn ich mir das zusammenbastele sieht das so aus ->


    SQL
    SELECT sorta.value, sorta.field_id, sortb.value, sortb.field_id, content.*, fieldvalues.* 
    FROM #__content AS content, #__fields_values AS fieldvalues 
    WHERE content.catid=52 
    LEFT JOIN (#__fields_values AS sorta) 
    ON (content.id = sorta.item_id AND sorta.field_id=417) 
    LEFT JOIN (#__fields_values AS sortb) 
    ON (content.id = sortb.item_id AND sortb.field_id=46) 
    ORDER BY sorta.value, sortb.value;

    ich kriege dann den Fehler 1064, dass im JOIN etwas falsch ist ...


    Error: 1064

    You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near

    'LEFT JOIN (#__fields_values AS sorta) ON (content.id = sorta.item_id AND sort' at line 1



  • Die fieldvalues brauchst du ja oben im ersten Bereich des SELECT nicht mehr. Hier kannst du sie auch nicht mehr einbinden, weil #__fields_values AS fieldvalues weg ist. Du greifts über sorta.value beziehungsweise sortb.value darauf zu. Und dann muss das WHERE hinter die JOINS.


    Ich denke so:

    SQL
    SELECT sorta.value, sorta.field_id, sortb.value, sortb.field_id, content.*
    FROM #__content AS content
    LEFT JOIN (#__fields_values AS sorta)
    ON (content.id = sorta.item_id AND sorta.field_id=417)
    LEFT JOIN (#__fields_values AS sortb)
    ON (content.id = sortb.item_id AND sortb.field_id=46)
    WHERE content.catid=52
    ORDER BY sorta.value, sortb.value;
  • astrid


    Hab vielen lieben Dank ... das funktioniert super .... da fehlt mir einfach die Erfahrung muss ich ehrlich sagen ... auch lieben Dank, dass Du es super erklärt hast! Da werden sich die Damen und Herren des Heimatvereins freuen :-)


    Ich muss mich damit unbedingt noch näher befassen und da noch mehr Basiswissen erlernen.


    Bisher habe ich die SQL-Abfragen immer als reines SQL belassen. Macht es Sinn, diese auf Joomla umzusetzen oder was wird dadurch besser? Ist es mehr eine Sicherheitsfrage?


    astrid Jetzt passiert aber folgendes ....


    Es sind 123 Bücher in der Literaturdatenbank. Wenn ich nach Autor sortiere werden mir 123 Bücher angezeigt. Wenn ich allerdings bisher mit dem alten SQL die Anzahl der Bücher abfragte, welche sich in der örtlichen Sortierung mit dem Bereich Dortmund befassten, so waren es 8.

    Mit der neuen Sortierung sind es alle 123. Wie kann ich die Anzahl der anzuzeigenden Datensätze auf die begrenzen, die durch sorta ausgefiltert wurden?

  • Danke astrid - ich hatte heute keine Zeit, du weisst ja ... und im Erklären bist du unschlagbar.


    seoBOXX ja, es ist unter anderem eine Sicherheitsfrage.

    In eigenen Erweiterungen, die nicht weitergegeben werden, ist es nicht so kritisch wie im Joomla Core. Im Joomla Core könnte es ja sein dass eine andere Datenbasis darunterliegt und natürlich ist deine Abfrage völlig ungeschützt gegen SQL injection. (Ich erkläre das nicht ...)

    Aus meiner Sicht sollte jeder, der in Joomla programmiert, sich damit vertraut machen.