Spaltenwerte zweier Tabellen vergleichen; dabei eine vom Typ BLOB

[HendriX]

Hallo allerseits!

Ich habe zwei Tabellen. Eine für Produkte, und eine für Mitglieder. Die Produkte sind nach Kategorien [cat_id] aufgeteilt. Die bevorzugten Produkt-Kategorien der Mitglieder werden in einem serialisierten BLOB-Feld [fav_cats] erfasst. Daher die LIKE-Klausel mit den Wildcards...

Folgendermaßen funktioniert meine Abfrage nach Produkten, die ein konkretes Mitglied bevorzugt, einwandfrei. Doch sehr elegant sieht die Lösung nicht aus, und ich muss für jede Produkt-Kategorie manuell eine extra Zeile hinzufügen...

SELECT id
  FROM products
 WHERE (cat_id = 1 AND (SELECT id FROM member WHERE fav_cats LIKE '%"1"%'))
    OR (cat_id = 2 AND (SELECT id FROM member WHERE fav_cats LIKE '%"2"%'))
    OR (cat_id = 3 AND (SELECT id FROM member WHERE fav_cats LIKE '%"3"%'))

Nun meine Frage: Wie kann ich diese Statement kompakter und so schreiben, dass die cat_id mit den fav_cats der zweiten Tabelle "automatisch" verglichen wird? Vielleicht kann man hier mit einer benutzerdefinierten Variablen, oder so, arbeiten..!?
Vielen Dank für einen Ratschlag dazu!

 

[akretschmer]

Hallo allerseits!

Ich habe zwei Tabellen. Eine für Produkte, und eine für Mitglieder. Die Produkte sind nach Kategorien [cat_id] aufgeteilt. Die bevorzugten Produkt-Kategorien der Mitglieder werden in einem serialisierten BLOB-Feld [fav_cats] erfasst. Daher die LIKE-Klausel mit den Wildcards...

Das klingt nach einem schlechten DB-Modell.

Nun meine Frage: Wie kann ich diese Statement kompakter und so schreiben, dass die cat_id mit den fav_cats der zweiten Tabelle "automatisch" verglichen wird? Vielleicht kann man hier mit einer benutzerdefinierten Variablen, oder so, arbeiten..!?
Vielen Dank für einen Ratschlag dazu!

Normalisiere das. Du brauchst eine dritte Tabelle, welche Deine Mitglieder und deine Produkte verbindet. Nutze dazu Primary Keys / Foreign Keys. Gehe nicht über MyISAM.
Bonus-Punkte würdest Du (von mir) für PostgreSQL bekommen ;-)


Andreas

 

[HendriX]

Hallo Andreas,

danke für Deine Einschätzung! Dass das DB-Modell nicht ideal ist, weiss ich.

Nichts desto trotz sehe ich mich derzeit dazu gezwungen, bei diesem Modell zu bleiben!

Daher würde ich hier gerne auf meiner Frage beharren, ob SQL für diese Konstellation nicht eine elegantere/rationellere Abfrage-Methode bereithält?

 

[akretschmer]

Hallo Andreas,

danke für Deine Einschätzung! Dass das DB-Modell nicht ideal ist, weiss ich.

Nichts desto trotz sehe ich mich derzeit dazu gezwungen, bei diesem Modell zu bleiben!

Daher würde ich hier gerne auf meiner Frage beharren, ob SQL für diese Konstellation nicht eine elegantere/rationellere Abfrage-Methode bereithält?

Ich könnt Dir in vielleicht 10 Minuten oder so eine Stored Proc in pl/pgsql frickeln, die das macht. Ich mache es aber nicht, weil Du eh MySQL hast und das Deine Schmerzen mit der DB nur zementieren würde. Mach es richtig, jetzt.

 

[akretschmer]

Ich könnt Dir in vielleicht 10 Minuten oder so eine Stored Proc in pl/pgsql frickeln, die das macht. Ich mache es aber nicht, weil Du eh MySQL hast und das Deine Schmerzen mit der DB nur zementieren würde. Mach es richtig, jetzt.

Außerdem hab ich Zweifel, ob das überhaupt funktioniert:

WHERE (cat_id = 1 AND (SELECT id FROM member WHERE fav_cats LIKE '%"1"%'))

Was passiert, wenn das innere Select eine Liste liefert? Aber auch, wenn es nur z.B. 5 liefert:

WHERE (cat_id = 1 AND (5))

Andreas

 

[HendriX]

Also gut... Danke!! Bin ja erst am Anfang meines SQL- und DB-Design-Verständnisses.

Aber ich dachte mir auch schon, dass man da am besten jeweils extra Tabellen für hat.. und dann "regulär" mit JOINs und so arbeiten kann...

Im Moment geht es darum, möglichst schnell eine Beta-Modell zu präsentieren. Daher sehe ich gerade nicht die Zeit, mich da weiter reinzufriemlen.. Aber für die endgültige Lösung werde ich mich dann um eine bessere Architektur bemühen; bzw. darum "bemühen lassen"... ; o )

 

[ukulele]

Ich verstehe die ursprüngliche Abfrage schon nicht. Du musst ja auf Bedingungen prüfen. Das wäre zunächst cat_id = 1 was entweder wahr oder falsch ist. Danach "prüfst" du auf Subselect? Soll das ein EXISTS sein? Was hat die Member ID mit deiner Produktkategorie zu tun? Oder bin ich jetzt gaga?

MSSQL mein dazu nur:
Meldung 4145, Ebene 15, Status 1, Zeile 3
In der Nähe von ')' wurde ein nicht boolescher Ausdruck in einem Kontext angegeben, in dem eine Bedingung erwartet wird.

 

[HendriX]

Außerdem hab ich Zweifel, ob das überhaupt funktioniert:

WHERE (cat_id = 1 AND (SELECT id FROM member WHERE fav_cats LIKE '%"1"%'))

Was passiert, wenn das innere Select eine Liste liefert? Aber auch, wenn es nur z.B. 5 liefert:

WHERE (cat_id = 1 AND (5))

Andreas

Doch, funktionieren tut es so ja bereits bei mir - schein's ohne Probleme! Diese Lösung fand ich durch "Rumprobieren".. Wenn ich es richtig verstehe, dann müssen hier eben beide WHERE-Klauseln TRUE zurückgeben, damit die ID des jeweiligen Produkt-Eintrags tatsächlich ausgewählt wird..

Was soll jetzt "5" für eine Klausel sein? 5 ist 5, oder? Also TRUE!?

 

[HendriX]

Ich verstehe die ursprüngliche Abfrage schon nicht. Du musst ja auf Bedingungen prüfen. Das wäre zunächst cat_id = 1 was entweder wahr oder falsch ist. Danach "prüfst" du auf Subselect? Soll das ein EXISTS sein? Was hat die Member ID mit deiner Produktkategorie zu tun? Oder bin ich jetzt gaga?

Man sehe mir nach. Bin noch ziemlich neu im SQL-Land.. Bei mir kommt aber kein Fehler. Und, wie gerade schon gesagt, das zweite SELECT-Konstrukt sollte (meiner Meinung nach) einfach valide sein (die ID wird ja sonst nicht gebraucht), und TRUE oder FALSE zurückgeben...

 

[akretschmer]

Doch, funktionieren tut es so ja bereits bei mir - schein's ohne Probleme! Diese Lösung fand ich durch "Rumprobieren".. Wenn ich es richtig verstehe, dann müssen hier eben beide WHERE-Klauseln TRUE zurückgeben, damit die ID des jeweiligen Produkt-Eintrags tatsächlich ausgewählt wird..

Was soll jetzt "5" für eine Klausel sein? 5 ist 5, oder? Also TRUE!?

MySQL's wundersame Welt ...

mysql> select * from foo;
+------+
| i    |
+------+
|    1 |
|    1 |
+------+
2 rows in set (0.02 sec)

mysql> select * from foo where 5;
+------+
| i    |
+------+
|    1 |
|    1 |
+------+
2 rows in set (0.01 sec)

versus

test=*# select * from foo;
 i
---
 1
(1 row)

test=*# select * from foo where (5);
ERROR:  argument of WHERE must be type boolean, not type integer
LINE 1: select * from foo where (5);

 

[ukulele]

Beschreib doch mal was du überhaupt ausgeben möchtest, bisher müsstest du alle Datensätze der products Tabelle mit cat_id 1 2 oder 3 ausgeben, egal was in deiner anderen Tabelle steht oder nicht steht.

 

[HendriX]

@akretschmer - Sorry, was willst Du mir jetzt damit sagen? Das das Argument hier vom Typ boolean sein muss ist wohl (auch mir) klar...

 

[HendriX]

@ukulele - Du hast völlig recht. Streng genommen hängt da jeweils noch eine Abfrage nach der user_id des angemeldeten Mitglieds hinten dran... Das wird vom CMS dynamisch über ein InsertTag "thisUser" eingesetzt... Wusste nur nicht, wie ich das hier in der Syntax visualisiere, und ließ es der (vermeintlichen ; o ) Übersichtlichkeit halber weg.. war blöd von mir.

 AND user_id = thisUser

SELECT id
  FROM products
 WHERE (cat_id = 1 AND (SELECT id FROM member WHERE fav_cats LIKE '%"1"%' AND user_id = thisUser))
    OR (cat_id = 2 AND (SELECT id FROM member WHERE fav_cats LIKE '%"2"%' AND user_id = thisUser))
    OR (cat_id = 3 AND (SELECT id FROM member WHERE fav_cats LIKE '%"3"%' AND user_id = thisUser))

 

[akretschmer]

@akretschmer - Sorry, was willst Du mir jetzt damit sagen? Das das Argument hier vom Typ boolean sein muss ist wohl (auch mir) klar...

Ja, ist es aber nicht. Es kann NULL, ein einzelner Wert oder eine Liste von Werten sein. Je nachdem, was das SELECT da rauspurzeln läßt. Dem MySQL ist so fast alles auf der Welt egal, daher kommt kein Syntaxfehler. Aber sehr wahrscheinlich auch kein korrektes Resultat.

 

[ukulele]

SELECT    p.id
FROM    products p
WHERE EXISTS (    SELECT    1
                FROM    member m
                WHERE    m.user_id = thisUser
                AND        m.fav_cats LIKE ( '%"' + p.cat_id + '"%' ) )

So in etwa stell ich mir das in SQL vor...