[MySQL 5.0] AND statement over onbekend aantal OR statements

[ThunderNet]

Het probleem:

Om bepaalde dingen aan elkaar te koppelen, met bepaalde eisen ben ik tot een aantal tabellen gekomen.
In de tabel groups wordt een bepaalde groep gedefineerd, een groep kan een n-aantal producten bevatten die in de tabel groupmembers gedefineerd staan.
Met de tabel groupconnections kan ik achterhalen welke groepen met elkaar in verband staan. Hieronder volgt eerst een algemene beschrijving van de relevante tabellen, daaronder wat voorbeelddata zoals deze in de tabellen kunnen staan.

Tabellen:

Table 'groups'

veldnaam	type	referentie
id	int
detail	varchar
productcategorie	int	refereert naar externe tabel

Table 'groupmembers'

veldnaam	type	referentie
groupsid	int	Refereert naar id in de tabel 'groups'
prodid	int	refereert naar id in externe tabel

Table 'groupconnectiontypes'

veldnaam	type	referentie
id	int
detail	varchar

Table 'groupconnections'

veldnaam	type	referentie
productgroup_koppelproduct	int	refereert naar id in tabel 'groups'
productgroup_voorwaarde	int	refereert naar id in tabel 'groups'
connectiontype	int

Voorbeelddata

SQL:

SELECT * FROM groupconnections g;

productgroup_koppelproduct	productgroup_voorwaarde	connectiontype
1	2	1
3	4	2
3	5	2
6	7	3

SQL:

SELECT * FROM groupmembers g;

groupsid	prodid
7	637
4	686
4	688
4	693
5	1273
5	1293
5	1318
6	1664
1	1702
1	1703
1	1704
2	1705
2	1706
2	1707
3	1766

Situaties

Als we naar de groupconnections tabel in de eerste row kijken, zien we dat group 1 aan group 2 gekoppeld wordt. Dit betekend dat een product uit group 1 (1702, 1703, 1704) uit een product van group 2 (1705, 1706, 1707) gekoppeld moet worden. Dus: (1702 OR 1703 OR 1704) AND (1705 OR 1706 OR 1707).
Dit is ansich nog niet zo'n groot probleem.
Echter, wil ik ook het volgende kunnen, zoals de situatie in de groupconnections tabel, met productgroup_koppelproduct. Hier komt in 2 rijen het productgroup_koppelproduct = 3 voor.. Overe meerdere rijen zouden de resultaten met een AND operator gekoppeld moeten worden dus: group 3 (1766) AND group 4 (686 OR 688 OR 693) AND group 5 (1273 OR 1293 OR 1318).
Waarbij group 3 in de geval 1 product bevat, maar ook een n-aantal had kunnen bevatten.

In de onderstaande sql code staat de connectiontype(=2) die ik refereer nog hardcoded, dit wordt later een parameter.

SQL:

SELECT groupsid, prodid
  FROM groupmembers
  WHERE groupsid IN(
    SELECT productgroup_voorwaarde
    FROM groupconnections
    WHERE connectiontype = 2
    )
;

Resultaat:

groupsid	prodid
4	686
4	688
4	693
5	1273
5	1293
5	1318

Wat wil ik nou uiteindelijk

Connectiontype als parameter meegeven, en een n-aantal parameters.
Als ik connectiontype = 2 heb, wil ik dus (OR op alle producten uit group=3) AND (OR op alle producten uit group=4) AND (OR op alle producten uit group=5).
In het geval van connectype = 1, wil ik dus (OR op alle producten uit group=1) AND (OR op alle producten uit group=2)
Hoevaak er dus een AND op een aantal producten moet gedaan worden hangt af van de tabel groupconnections.

Het liefste zou ik als parameter in mijn sql willen meegeven welke connectiontype het is, en een n-aantal productid's. Connectiontype = 2, products(1766, 688, 1293) dit zou een true moeten retourneren. Want 1766 IN (group3) = true, AND 688 IN(group4) = true, AND 1293 IN(group5) = true.

In het geval van: Connectiontype = 2, products(1766, 555, 1293) wil ik false terug krijgen. Want 1766 IN (group3) = true, AND 555 IN(group4) = false, AND 1293 IN(group5) = true.

Hopelijk kan iemand mij in de juiste richting duwen, eigenlijk wil ik dus een soort van foreach lus in sql voor elkaar krijgen. Ik zit gewoon even compleet vast in mijn gedachten gang hierover. Alvast bedankt voor alle hulp en duwtjes in de juiste richting.

[Voutloos]

Maak een loopje welke alle voorwaarden juist met OR achter elkaar zet. En kijk dan of num_rows of count(*) gelijk is aan het aantal voorwaarden. Klaar.

De query zelf is dan dus vrij basic, zonder geneuzel met self joins, unions, subqueries of tig IN() voorwaarden, en daarmee is dit, als je door hebt waarom je voor mijn aanpak rijen wil tellen, een van de leesbaardere en snellere queries voor dit probleem.

[ Voor 46% gewijzigd door Voutloos op 06-05-2009 19:51 ]

[ThunderNet]

Maar dan zou ik er weer applicatiecode omheen moeten bouwen, wat ik liever niet doe. Ik zoek dus een manier om op de een of andere manier een logica op élke row toe te passen in SQL.

[Voutloos]

Applicatielogica? 1 foreach met 1 string concatenation erin om de where clause op te bouwen.

[Matis]

Voutloos schreef op donderdag 07 mei 2009 @ 09:09:
Applicatielogica? 1 foreach met 1 string concatenation erin om de where clause op te bouwen.

Ik snap wat je bedoelt, maar de TS geeft duidelijk aan dat hij het op SQL niveau wil opvangen. Ik weet ook niet de omvang van het tabel en de complexiteit van de methodes, maar ik kan me goed voorstellen dat een SQL server het sneller kan oplossen dan een applicatie code, mits de goede invulling/argumenten.

@TS
Ik heb mijn hoofd even zitten breken over de schets van je situatie, alleen staat er in je Table 'groupconnections' bij twee velden "refereert naar id in tabel 'groups'" maar het is een varchar, misschien doel je daar op detail

Derhalve ben ik zo'n constructie welke jij wilt nog nooit zelf tegen gekomen en ik heb ook niet een idee hoe ik (jij) dit 1 2 3 kan oplossen

Ik zal eens wat navraag doen en als ik een goede oplossing heb dan hoor je het van me !

[ Voor 13% gewijzigd door Matis op 07-05-2009 09:18 ]

[Voutloos]

toaomatis schreef op donderdag 07 mei 2009 @ 09:16:
, maar ik kan me goed voorstellen dat een SQL server het sneller kan oplossen dan een applicatie code

Daar had ik al in het wilde weg een uitspraak over gedaan:

Voutloos schreef op woensdag 06 mei 2009 @ 19:41:
De query zelf is dan dus vrij basic, zonder geneuzel met self joins, unions, subqueries of tig IN() voorwaarden, en daarmee is dit, als je door hebt waarom je voor mijn aanpak rijen wil tellen, een van de leesbaardere en snellere queries voor dit probleem.

De logica zit toch echt gewoon in de query, je moet enkel dynamisch een where clauseje bouwen. Succes met de sql aanpak.

[ThunderNet]

toaomatis schreef op donderdag 07 mei 2009 @ 09:16:
[...]

Die varchar is een klein foutje, dat moet gewoon een int zijn. Beide velden refereren dus naar de id's in de tabel groups en zo onstaat de koppeling.
Elk veld verwijst naar een group, en op die manier kun je achter de productid's in groupmembers komen. groups zijn dus een verzameling van producten. En als ik een group connection maak tussen groep 1 en groep 2, dan moet aan de voorwaarde gedaan worden dat een product uit groep 1 én een product uit groep 2 meegegeven worden.
Zijn er 2 regels in de groupconnections (zoals in het voorbeeld met group 3) dan moet een product uit groep 1 én een product uit groep 2 én een product uit groep 3 meegegeven worden.

---

Met de volgende query kom ik al een redelijk end.

SQL:

SELECT  (IF(gm1.prodid IN(686, 1293, 1766), '1', '0')) as score, gc.connectiontype, gm1.prodid, g1.id as groupid FROM groupconnections gc
LEFT JOIN groups g1
  ON g1.id = gc.productgroup_voorwaarde OR g1.id = gc.productgroup_koppelproduct
LEFT JOIN groupmembers gm1
  ON gm1.groupsid = g1.id
LEFT JOIN contentproducts cp1
  ON cp1.id = gm1.prodid
WHERE gc.connectiontype = 2
GROUP BY gm1.prodid
;

Waarbij de parameters de connectiontype = 2, en de lijst met producten in de IN.

Deze query geeft het volgende resultaat:

score	connectiontype	prodid	groupid
'1'	2	686	4
'0'	2	688	4
'0'	2	693	4
'0'	2	1273	5
'1'	2	1293	5
'0'	2	1318	5
'1'	2	1766	3

Waarbij ik dus eigenlijk minimaal 1 score = 1 per groupid wil.

[ Voor 43% gewijzigd door ThunderNet op 07-05-2009 10:18 ]

[ThunderNet]

Goed zoals het nu lijkt heb ik het opgelost met de volgende query:

SQL:

SELECT (IF(sum(resultaat) >= count(groupid), 'true', 'false')) as eindresultaat
FROM (
  SELECT (IF(sum(score) >= 1, '1', '0')) as resultaat, groupid
    FROM (
        SELECT DISTINCT (IF(gm1.prodid IN(688, 1766, 1273), '1', '0')) as score, gc.connectiontype, gm1.prodid, g1.id as groupid, cp1.shortName FROM groupconnections gc
        LEFT JOIN groups g1
          ON g1.id = gc.productgroup_voorwaarde OR g1.id = gc.productgroup_koppelproduct
        LEFT JOIN groupmembers gm1
          ON gm1.groupsid = g1.id
        LEFT JOIN contentproducts cp1
          ON cp1.id = gm1.prodid
        WHERE gc.connectiontype = 2

      ) AS tmp
  GROUP BY groupid)
AS tmp2;