Sprache / Alternativsprache per SELECT

  • Sprache / Alternativsprache per SELECT

    Basierend auf diesem Thread: Eine SQL Abfrage für Sprache oder Alternativsprache

    Hallo SQL-Freaks!

    Ich möchte unsere Anwendung internationalisieren und hadere mit dem Erstellen eines geeigneten SQL-Statements. Die Anforderung ist die Elemente mit einem Query so auszulesen, daß ich die Übersetzungen in einer gegebenen Sprache (trans_lang_id) erhalte wenn diese vorhanden sind, ansonsten den jeweiligen Text einer StandardSprache (default_lang_id).

    Meine Tabellenstruktur:

    Da die Anzahl der Sprachen dynamisch sein soll sind diese in einer gesonderten Tabelle LANG gespeichert.

    TABLE lang
    - id INT AUTOINCREMENT
    - code VARCHAR
    - name VARCHAR

    Z.B.:

    id, code, name
    1, en, English
    2, de, Deutsch
    3, fr, Francais

    Die übersetzbaren Elemente der DB habe ich jeweils in einen sprachunabhängigen Teil und einen sprachabhängigen Teil zergliedert:

    TABLE element
    - element_id INT AUTOINCREMENT
    - general_data VARCHAR

    Z.B.:

    id, general_data
    1, "item 1"
    2, "item 2"

    TABLE element_lang
    - element_lang_id INT AUTOINCREMENT
    - element_id INT
    - lang_id INT
    - translatable_data VARCHAR

    Z.B.:

    id, element_id, lang_id, translatable_data
    1, 1, 1, "english text"
    2, 1, 2, "deutscher Text"
    3, 1, 3, "text francais"
    4, 2, 1, "another english text"
    /* "item 2" ist nicht ins deutsche oder französische übersetzt! */

    Ich hatte mir etwa folgendes überlegt:

    Quellcode

    1. SELECT element.*, element_lang.translatable_data
    2. FROM element, element_lang
    3. WHERE element.element_id=element_lang.element_id
    4. AND (
    5. (element_lang.lang_id=trans_lang_id AND NOT ISNULL(element_lang.id))
    6. OR element_lang.lang_id=default_lang_id
    7. );


    Dieses Statement bringt mich aber nicht weiter, da für übersetzte Elemente auch die Datensätze der Standardsprache ausgelesen werden. Wie kann ich das verhindern?

  • Hi.
    es stimmt schon, bei deiner Lösung kann man den Subselect durch einen weiteren JOIN ersetzen. Aber ob der Code dadurch kürzer wird?

    Lass mich dir erstmal dein aktuelles Ergebnis durch einen performanteren JOIN ersetzen.
    Den CROSS JOIN habe ich durch einen NATURAL JOIN ersetzt. Dadurch sparst du dir die erste WHERE Bedingung und bei der zweiten fallen die NULL Werte weg.

    Quellcode

    1. SELECT element.*, A.translatable_data
    2. FROM element
    3. NATURAL JOIN element_lang A
    4. WHERE A.lang_id = {{trans_lang_id}}
    5. OR A.lang_id = {{default_lang_id}};


    Mit dieser Ausgabe könntest du dir schon mit ein in weiteren PHP Schleife die Lösung zusammenbauen. Aber das wollen wir ja nicht. SQL ist performanter!!!

    Weiter kommst du aber auch nicht ohne einen weiteren JOIN. Schließlich willst du das Ergebnis in einer Zeile haben..... also joinen

    Quellcode

    1. SELECT element.*, A.translatable_data
    2. FROM element
    3. NATURAL JOIN element_lang A
    4. LEFT JOIN element_lang B
    5. ON A.element_id = B.element_id
    6. WHERE B.lang_id = {{trans_lang_id}}
    7. OR A.lang_id = {{default_lang_id}};


    Marcus Gnaß schrieb:

    Zudem halte ich die IF-Abfragen pro auszulesender Spalte für nicht sehr praktikabel wenn man einige zig Spalten ausliest.

    Aber wann willst du sie sonst machen? Ich behaupte mal MySQL kann if/else schneller ausführen als wenn du erstmal die Null Werte per Socket an PHP überträgst und danach die if/else Unterscheidung in PHP machst. Daher muss sie hier auch wieder hinein, SQL ist performanter ;)

  • Vielen Dank für deine Antwort!

    Den NATURAL JOIN werde ich bei mir durch einen äquivalenten LEFT JOIN ersetzen müssen, aber wieso meinst du, daß dieser performanter als der CROSS JOIN sei?

    Mein Konstrukt mit dem ISNULL war natürlich völliger Blödsinn. Ich hatte nur gehofft, daß ich "bedingt joinen" kann. Also einmal mit der Tabelle element_lang mit lang_id = {{trans_lang_id}} wenn vorhanden, ansonsten mit lang_id = {{default_lang_id}}.

    Aber genau das scheint nicht zu gehen, so daß ich doch zweimal mit element_lang joinen muß. Das bedeutet aber, daß ich statt der bisherigen Tabelle element (die sowohl die sprachabhängigen als auch die sprachunabhängigen Daten enthielt) plötzlich mit drei Tabellen (einmal element und zweimal element_lang) arbeiten muß. Das ist nicht gerade prickelnd!

    d0nut schrieb:




    Marcus Gnaß schrieb:

    Zudem halte ich die IF-Abfragen pro auszulesender Spalte für nicht sehr praktikabel wenn man einige zig Spalten ausliest.
    Aber wann willst du sie sonst machen? Ich behaupte mal MySQL kann if/else schneller ausführen als wenn du erstmal die Null Werte per Socket an PHP überträgst und danach die if/else Unterscheidung in PHP machst. Daher muss sie hier auch wieder hinein, SQL ist performanter ;)
    An dieser Stelle war meine Beschreibung ungenau. Angenommen ich habe in der Tabelle element_lang mehrere übersetzbare Daten/Spalten. Dann soll nicht pro Datum/Spalte entschieden werden ob die Übersetzung oder das Original verwendet wird, sondern nur pro Element. Soll heißen: wenn das Element eine Übersetzung hat (ein Datensatz der entsprechenden Sprache in element_lang existiert) soll diese verwendet werden, wenn nicht dann die der Standardsprache.

    EDIT: Du hast natürlich Recht! Ich verwende MySQL! Aber kein PHP sondern Sun One ASP. :(

  • Marcus Gnaß schrieb:

    Den NATURAL JOIN werde ich bei mir durch einen äquivalenten LEFT JOIN ersetzen müssen, aber wieso meinst du, daß dieser performanter als der CROSS JOIN sei?

    Ich kenne das nur von Oracle (und das auch nur von Hörensagen), dass der Query Optimizer das ausreichend schnell optimiert.
    Als ich meinen letzten Performancetest mit MySQL, ausreichend Daten und einem EXPLAIN davor gemacht habe, wurde aber keine Queryoptimierung durchgeführt.
    Und wenn diese nicht durchgeführt wird, dann wird erstmal die gesamte Anfrage ohne WHERE Bedingung durchgeführt. Was ja n*n Zeilen gibt, weil jede Zeile mit jeder Zeile verknüpft wird
    Erst DANACH wird das Ergebnis mit der WHERE Bedinung eingeschränkt (bei einer 1-1 Verbindung also wieder auf n Zeilen reduziert).
    Bei einem INNER JOIN werden nur die n Zeilen in den Speicher gelesen.

    Warum willst du den NATURAL JOIN durch einen NATURAL LEFT JOIN ersetzen? Muss die Defaultsprache nicht komplett sein?

    Marcus Gnaß schrieb:

    Dann soll nicht pro Datum/Spalte entschieden werden ob die Übersetzung oder das Original verwendet wird, sondern nur pro Element.

    Mit der IF Syntax in SQL wird das auf jede Zeile einzeln geprüft. Kostet natürlich jedesmal eine Abfrage. Aber ob in der Datenbank oder in ASP - irgendwann muss die Abfrage gemacht werden.

  • d0nut schrieb:



    Marcus Gnaß schrieb:

    Den NATURAL JOIN werde ich bei mir durch einen äquivalenten LEFT JOIN ersetzen müssen, aber wieso meinst du, daß dieser performanter als der CROSS JOIN sei?
    Warum willst du den NATURAL JOIN durch einen NATURAL LEFT JOIN ersetzen? Muss die Defaultsprache nicht komplett sein?

    Marcus Gnaß schrieb:

    Dann soll nicht pro Datum/Spalte entschieden werden ob die Übersetzung oder das Original verwendet wird, sondern nur pro Element.
    Mit der IF Syntax in SQL wird das auf jede Zeile einzeln geprüft. Kostet natürlich jedesmal eine Abfrage. Aber ob in der Datenbank oder in ASP - irgendwann muss die Abfrage gemacht werden.
    Ob eine Übersetzung komplett ist oder nicht wird in der Verantwortung der Benutzer liegen. Entscheidend ist nur, OB eine Übersetzung vorliegt (also ein entsprechender Datensatz existiert). Deshalb reicht bei mir auch:

    Quellcode

    1. SELECT element.id
    2. , IF(ISNULL(element_lang_trans.id), element_lang_default.data_a, element_lang_trans.data_a) data_a
    3. , IF(ISNULL(element_lang_trans.id), element_lang_default.data_b, element_lang_trans.data_b) data_b
    4. , IF(ISNULL(element_lang_trans.id), element_lang_default.data_c, element_lang_trans.data_c) data_c
    5. ...


    Allerdings bin ich jetzt auf eine andere Idee gekommen. Ich notiere im Element einfach für welche Sprachen dieses übersetzt ist (lang_trns als CSV). Zudem notiere ich mir pro Element noch die ID einer Defaultsprache für die immer eine "Übersetzung" (lang_dflt) existiert. Dann kann ich nämlich mit folgendem Statement arbeiten:

    Quellcode

    1. SELECT element.id
    2. , element_lang.data_a
    3. , element_lang.data_b
    4. , element_lang.data_c
    5. FROM element
    6. LEFT JOIN element_lang
    7. ON element.id=element_lang.element_id
    8. AND element_lang.lang_id=IF(INSTR(CONCAT(',',element.lang_trns,','), CONCAT(',','{{trans_lang_id}}',',')), {{trans_lang_id}}, element.lang_dflt)
    9. ;