MySQL concat, returnere non-matches, ORDER BY efter mængden af disse osv.

Der mangler lidt i min query for at holde styr på hvor min hoved query er:



Jeg vil også gerne kunne sortere alle resultater der mangler mere end 2 ingredienser fra, og alle resultater der ikke indeholder nogen af de give ingrdienser.
Ovenfor, sorterer jeg selv de nævnte resultater fra med PHP, men det betyder at min query løber unødigt mange resultater igennem.

Jeg ville gerne have noget ud der minder om:
opskrift: navn1
Har:
ost
sovs
nisser

opskrift: navn2
Har:
foo
bar

Jeg behøver ikke nødvendigvist noget funktionelt, bare nogle skub i den rigtige retning, PHP-delen skal jeg nok selv finde ud af.

Ok, nu gør jeg selv hvad irriterer mig når andre laver et spørsmål; jeg fortæller ikke præcist hvad jeg vil.

Jeg har en gruppe ingredienser fra et array, som jeg gerne vil sammenligne med nogle opskrifter fra en database;

opskrifter:
+-----+------+
| oid | navn |
+-----+------+

ingredienser:
+-----+-----+------------+
| iid | oid | ingrediens |
+-----+-----+------------+

de skal selvf. joines osv, men for lige at tage lidt rod ud af en i forvejen rodet query, valgte jeg bare i mine tidligere eksempler at bruge navn.

Anyways.
Planen er at man skal kunne finde fulde og delvise matches, og at man skal kunne styre hvor mange ingredienser der må mangle.
Ydermere, skal man kunne bygge en liste over de ingredienser man har og dem man mangler.

Kriterierne er:
x = specificeret max  tilladte antal manglende ingredienser
y = manglende ingredienser
z = ingredianser man har

y <= x && z >= 1


Alt sammen vil jeg gerne forsøge at lave i en enkelt query, hvilket efter min mening gør GROUP_CONCAT() en hel åbenlys kandidat.

Nogen forslag til ændringer/forbedringer af mine tidligere foreslag, eller helt andre metoder?

Jeg er aabenbart ikke den eneste der har svaert ved at finde ud af hvad du vil.  De andre medlemmer af Eksperten ved det heller ikke, ellers havde du haft indlaeg nu.  Og, maaske er du heller ikke saa sikker?  Men lad mig goere nogle antagelser og stille nogle spoergsmaal for at klarlaegge HVAD du vil foer jeg proever at diskutere HVORDAN du opnaar det (om GROUP_CONCAT() nu er den aabenlyse kandidat eller ej.)

Kan det vaere at du vil lave et slags "Toem koeleskabet" funktion hvor du skriver op hvilke ingredienser du har og saa vil du vide hvilke opskrifter du naesten kan lave med de ingredienser og hvilke ingredienser du mangler?

Du har saa opskrifter med de noedvendige ingredienser i de to tabeller du viser i #2. 

Og de ingredienser du har putter du i en array.  Saa vil du sammenligne alle opskrifterne med arrayen, og for hver opskrift hvor antal manglende ingredienser ikke overstiger x vil du skrive ud hvilke af ingredienserne i arrayen der kan bruges til den opskrift.

Et spoergsmaal og en kommentar for naervaerende:

Spoergsmaal:  Hvordan faar du arrayen over ingredienser?  Har du en HTML formular hvor brugerne kan fylde ingredienser paa og saa trykke paa en knap naar de er faerdige?

Kommentar:  Jeg ville mene at oplysninger om hvilke opskrifter delvis kan laves med de ingredienser man har er temmelig uinteressant hvis man ikke ogsaa faar at vide hvilke ingredienser der mangler.  Altsaa:

Navn1: har ost, sovs, nisser - mangler lakrids og laksko.

Jeg tror mange skræmmes væk af 3 walls of text :P

Jeg ved præcist hvad jeg vil have :P
Jeg har lavet det hele i PHP med en query der fanger alt, og efterfølgende sortering i PHP, og lidt flere queries, hvilket er omstændigt (, langsomt?) og unødigt med den rette query.

Ja, det er lige præcis en "Tøm Køleskabet" funktion


Til dit spørgsmål:
Jeps, det er fra en side med en masse checkboxes.

Til din kommentar:
Nemlig, men det har jeg også beskrevet flere gange i mine 3 tidligere posts :)

At det ikke lige står i mine eksempler er fordi jeg glemte at tilføje et eksempel hvor der skulle være en "Mangler"


Hvis det hele ikke fremgår af mine tidligere posts, er det fordi jeg er en klovn til at formulere mig og fatte mig kort og præcist.


Query skal returnere:
'opskriftnavn'
'opskriftid'
'matches' mellem array og database
'mangler' mellem array og database
hvor 'mangler' er mindre end eller lig med 'x'
og 'matches' er mere end eller lig med 1
resultat sorteres stigende efter antal 'mangler'

Bare saa du ved det, jeg er i gang med at soege efter en loesning.  Det kommer nok til at tage lidt tid, for det foerste fordi jeg faar lidt travlt i dag, for det andet fordi det bliver en kompleks query.

hehe... det tror jeg gerne...
Jeg ved der er nogen der søger udfordringer herinde, mig selv bl.a. så jeg syntes da lige jeg ville give en :P

Jeg har søgt en hel del rundt omkring, for selv at kunne løse det, men nemt er det da vidst ikke lige...

Det er ærgeligt at man ikke kan bruge WHERE med GROUP_CONCAT(), så tror jeg at jeg selv kunne have klaret det...

Eller det kan man måske godt alligevel?

Jeg kan ikke finde hvor hulen det var at jeg læste det...

Men jeg ville sikkert stadig bøvle med mine non-matches, så jeg tror bare lige jeg holder mund til du vender tilbage :P

Beklager, jeg maa give op.

Jeg lavede en tabel med oid og ingrediens og en array med ingredienser og arrangerede det saaledes at de fleste men ikke alle opskrifter kraevede ingredienser der ikke var i arrayen.  Jeg proevede saa at bygge queryen op skridt for skridt, foerst finde de ingredienser arrayen har:

SELECT DISTINCT i.oid, GROUP_CONCAT(i.ingrediens) har
FROM ggxdgi i
WHERE i.ingrediens IN('ing_a', 'ing_c', 'ing_e', 'ing_h', 'ing_i', 'ing_k')
GROUP BY i.oid ;

Det gav, som forventet, dette resultat:
oid  har 
1 ing_a,ing_c,ing_e
2 ing_a
4 ing_c,ing_h,ing_e
5 ing_e,ing_c

Saa de ingredienser arrayen mangler, det samme bare med NOT IN.  Det gav, som forventet, dette resultat:

oid  mangler 
1 ing_b,ing_d,ing_f
2 ing_b,ing_g
3 ing_b
4 ing_d,ing_f
5 ing_g,ing_f,ing_d

Saa satte jeg de to sammen (jeg ved godt at jeg ikke har talt de manglende ingredienser endnu):

SELECT DISTINCT i.oid, GROUP_CONCAT(i.ingrediens) har, GROUP_CONCAT(m.ingrediens) mangler
FROM ggxdgi i
JOIN (SELECT DISTINCT oid, ingrediens FROM ggxdgi WHERE ingrediens NOT IN ('ing_a', 'ing_c', 'ing_e', 'ing_h', 'ing_i', 'ing_k') GROUP BY oid) m ON i.oid = m.oid
WHERE i.ingrediens IN('ing_a', 'ing_c', 'ing_e', 'ing_h', 'ing_i', 'ing_k')
GROUP BY i.oid ;

Men det gav et uventet resultat:
 
oid  har  mangler 
1 ing_a,ing_c,ing_e ing_b,ing_b,ing_b
2 ing_a ing_b
4 ing_c,ing_h,ing_e ing_d,ing_d,ing_d
5 ing_e,ing_c ing_g,ing_g

For eksempel for oid skulle det have vaeret
oid har mangler
1 ing_a, ing_c, ing_e, ing_b, ing_d, ing_f

altsaa i stedet for at faa de manglende ingredienser som ing_b, ing_d, ing_f faar jeg tre gange ing_b.  Jeg har proevet alt hvad jeg kunne taenke paa for at rette det, men forgaeves.  Der er nok noget ved GROUP_CONCAT som jeg ikke forstaar.  (For at vaere aerlig kendte jeg slet ikke GROUP_CONCAT foer jeg begyndte med dit spoergsmaal.

Jeg er loebet toer for tid, saa jeg maa slutte.  Held og lykke med at faa problemet loest fra anden side.

Der ser ud som om din subquery kun returnerer det første resultat.
Det ser også ud som om din subquery returnerer det samme antal mangler som den har.

Så for hvert "opslag" af hoved queryen, outputter subqueryen 1 og samme resultat?

Hvor meningen er at det normale table "i" og subqueryen "m" skal joines, med felterne
oid(i/m), i.ingredienser, m.ingredienser ?

Jeg anede ikke at man kunne behandle et subquery som en tabel, men det er da en rimelig cool måde at gribe tingene an på :)

SELECT DISTINCT i.oid, GROUP_CONCAT(i.ingrediens) har, GROUP_CONCAT(m.ingrediens) mangler
FROM (SELECT DISTINCT oid, ingrediens FROM ggxdgi WHERE ingrediens IN ('ing_a', 'ing_c', 'ing_e', 'ing_h', 'ing_i', 'ing_k') GROUP BY oid) i
JOIN (SELECT DISTINCT oid, ingrediens FROM ggxdgi WHERE ingrediens NOT IN ('ing_a', 'ing_c', 'ing_e', 'ing_h', 'ing_i', 'ing_k') GROUP BY oid) m ON i.oid = m.oid
GROUP BY i.oid

Ouputter ovenstående så det samme, eller første har og mangler?

Ulempen ved ovenstående er så selvf. at den nok også vil lave et output ved opskrifter hvor man ikke har nogen af ingredienserne, hvor dit foreslag er ude over det problem.

Man kunne evt. også se hvad de outputter uden concats.


Eller må jeg lige se om jeg kan finde koderne til min gamle støvede hjemmeside og teste det lidt selv :P

Jeg proevede lige din query paa min testdatabase og den gav dette resultat:
 
oid  har  mangler 
1 ing_a ing_b
2 ing_a ing_b
4 ing_c ing_d
5 ing_e ing_g

altsaa kun en 'har' og en 'mangler'.

"Man skal beundre den standhaftige  og afsky den staedige.  Den foerste er mig, den anden er min nabo."  Det er et citat jeg for laenge siden har glemt hvor kommer fra.  Men siden du saa stadigt pukker paa med dette spoergsmaal var jeg standhaftig nok til at proeve igen.  Jeg har nu:

oid  har  mangler 
1 ing_a,ing_c,ing_e ing_b,ing_d,ing_f
2 ing_a ing_b,ing_g
4 ing_c,ing_h,ing_e ing_d,ing_f
5 ing_e,ing_c ing_g,ing_f,ing_d

hvilket er det forventede resultat.  Saa mangler der stadig det at kun opskrifter med et maximalt antal manglende ingredienser skal med.

Querien jeg brugte var denne:

SELECT DISTINCT i.oid, GROUP_CONCAT(i.ingrediens) har, mi mangler
FROM ggxdgi i
JOIN (SELECT DISTINCT oid, GROUP_CONCAT(ingrediens) mi FROM ggxdgi WHERE ingrediens NOT IN ('ing_a', 'ing_c', 'ing_e', 'ing_h', 'ing_i', 'ing_k') GROUP BY oid) m ON i.oid = m.oid
WHERE i.ingrediens IN('ing_a', 'ing_c', 'ing_e', 'ing_h', 'ing_i', 'ing_k')
GROUP BY i.oid ;

Forklaring:  Subquerien "SELECT DISTINCT oid, GROUP_CONCAT(ingrediens) mi FROM ggxdgi WHERE ingrediens NOT IN ('ing_a', 'ing_c', 'ing_e', 'ing_h', 'ing_i', 'ing_k') GROUP BY oid" leverer de manglende ingredienser per opskrift.  Jeg giver "GROUP_CONCAT(ingredienser)" aliasen 'mi' (manglende ingredienser), og saa bruger jeg mi som en af de tre fields der skal vises.

Hvis jeg for tid skal jeg proeve at spekulere over hvordan man faar den begraenset til et maksimalt antal manglende ingredienser.

SELECT i.oid, GROUP_CONCAT( (

SELECT ingrediens
FROM aa_ingredienser
WHERE iid = i.iid
AND ingrediens
IN (
'vodka', 'pøls'
) )
)har, GROUP_CONCAT( (

SELECT ingrediens
FROM aa_ingredienser
WHERE iid = i.iid
AND ingrediens NOT
IN (
'vodka', 'pøls'
) )
)mangler
FROM aa_ingredienser i
GROUP BY oid


ser også ud til at virke, men jeg kan ikke få smidt COUNT ind nogen steder, så jeg kan simpelthen heller ikke finde ud af hverken hvordan jeg sorterer efter antal manglende indgredienser, eller lave en begrændsning på disse.
Utroligt at det skal vise sig at være blandt de største udfordringer.


Men med det jeg har lært indtil videre, så får du pointene ligemeget hvor meget mere du gider at arbejde med det, så smid et svar en gang når du lyster :)

Nu har jeg ikke lige testet det endnu, men jeg tror at man med din opbygning kan lave count på din overordnede query, så hvis man byttede om så manglende ingredienser bliver den overordnede, kunne man måske lave count på skidtet

SELECT DISTINCT i.oid, GROUP_CONCAT(i.ingrediens) har, mi mangler, mico
FROM aa_ingredienser i
JOIN (SELECT DISTINCT COUNT(*) mico, oid, GROUP_CONCAT(ingrediens) mi FROM aa_ingredienser WHERE ingrediens NOT IN ('tyrkisk peber', 'rød sodavand', 'vodka', 'pøls') GROUP BY oid) m ON i.oid = m.oid
WHERE i.ingrediens IN('tyrkisk peber', 'rød sodavand', 'vodka', 'pøls')
GROUP BY i.oid

Fungerer ganske fortrinligt.
Jeg har tilføjet et count til din subquery, og ellers er alt det samme.

Men jeg får ikke noget output hvor der ikke mangler nogen ingredienser :P

SELECT DISTINCT i.oid, GROUP_CONCAT(i.ingrediens) har, mi mangler, mico
FROM aa_ingredienser i
JOIN (SELECT DISTINCT COUNT(*) mico, oid, GROUP_CONCAT(ingrediens) mi FROM aa_ingredienser WHERE ingrediens NOT IN ('tyrkisk peber', 'rød sodavand', 'vodka', 'pøls') GROUP BY oid) m ON i.oid = m.oid
WHERE i.ingrediens IN('tyrkisk peber', 'rød sodavand', 'vodka', 'pøls')
AND mico <= 3
GROUP BY i.oid
ORDER BY mico

Hvis jeg bare kan få ovenstående til at outputte de resultater hvor der ikke mangler noget også, så ville det være helt perfekt :)

Har prøvet at bytte om på hvor 'har' og 'mangler' behandles, men det er med samme resultat, hvis der ikke er noget resultat fra enten 'har' eller 'mangler', så laver den ikke noget output :s

Det skal lige tilføjes at det gør ikke noget der ikke er noget output når man ikke har nogen af indgredienserne, men hvis man har alle, så giver den heller ikke noget.

Problemerne ligger vel i at der i subqueryen som der joines med, ikke returner noget, og at der derfor ikke kan joines?

Forsøger på at fikse det med LEFT JOIN, men uden held...

Jamen saa lad mig, jaevnfoer #12, oprette et svar og saa slutte med at begraense antallet af manglende ingredienser til, for eksempel, 2 med denne query.  Det nye er at jeg i subqueryen efter GROUP BY tilfoejer: 'HAVING COUNT(ingrediens) < 3'

SELECT DISTINCT i.oid, GROUP_CONCAT(i.ingrediens) har, mi mangler
FROM ggxdgi i
JOIN (SELECT DISTINCT oid, GROUP_CONCAT(ingrediens) mi FROM ggxdgi WHERE ingrediens NOT IN ('ing_a', 'ing_c', 'ing_e', 'ing_h', 'ing_i', 'ing_k') GROUP BY oid HAVING COUNT(ingrediens)<3) m ON i.oid = m.oid
WHERE i.ingrediens IN('ing_a', 'ing_c', 'ing_e', 'ing_h', 'ing_i', 'ing_k')
GROUP BY i.oid ;

giver
 
oid  har  mangler 
2 ing_a ing_b,ing_g
4 ing_c,ing_h,ing_e ing_d,ing_f

Det bliver ikke vist saa tydeligt her, men arrayen har for opskrift 2 en ingrediens a og mangler to, b og g.  For opskrift 4 har c, h, og e, mangler d og f.  De andre opskrifter har enten nul (3) eller mangler mere end to elementer (1 og 5).  Til reference og kontrol, her er tabellen med opskrifter of ingredienser:
     
iid  oid  ingrediens 
      1 1 ing_a
      2 1 ing_b
      3 1 ing_c
      4 1 ing_d
      5 1 ing_e
      6 1 ing_f
      7 2 ing_a
      8 2 ing_b
      9 2 ing_g
      10 3 ing_b
      11 4 ing_c
      12 4 ing_d
      13 4 ing_h
      14 4 ing_e
      15 4 ing_f
      16 5 ing_g
      17 5 ing_f
      18 5 ing_e
      19 5 ing_d
      20 5 ing_c

Saa er det stadig ikke sorteret efter antal having, og der er heller ingen php kode til at formattere det, men jeg maa af tidshensyn stoppe her.

PHP koden skal jeg nok selv tage mig af... det er mere SQL koden.
sortering og count har jeg fået styr på, men jeg kan simpelthen ikke få den til at outputte opskrifter man har alle ingredienser til :P

Men mange tak alligevel for al arbejdet :D

Den havde jeg ikke taenkt paa (opskrifter for hvilke arrayen har alle ingredienser.)

Jeg eksperimenterede derfor videre.  Til dette formaal indsatte jeg en sjette opskrift i tabellen med precis alle ingredienserne i arrayen og en syvende opskrift med faerre ingredienser end i arrayen.

Hvis jeg bruger en LEFT JOIN i stedet for en INNER JOIN (eller blot JOIN) faar jeg ganske vist den sjette opskrift men ogsaa de opskrifter hvor der er mere end to manglende ingredienser.

Saa jeg proevede med en UNION query hvor den foerste halvdel var den query vi allerede havde og den anden halfdel var en query med "WHERE NOT EXISTS(SELECT ......)

Her er queryen (undskyld at jeg lavede saa lang en query, men jeg havde ikke tid (eller forstand) til at lave en kort query):

SELECT DISTINCT i.oid, GROUP_CONCAT(i.ingrediens) har, mi mangler
FROM ggxdgi i
JOIN (SELECT DISTINCT oid, GROUP_CONCAT(ingrediens) mi FROM ggxdgi WHERE ingrediens NOT IN ('ing_a', 'ing_c', 'ing_e', 'ing_h', 'ing_i', 'ing_k') GROUP BY oid HAVING COUNT(ingrediens)<3) m ON i.oid = m.oid
WHERE i.ingrediens IN('ing_a', 'ing_c', 'ing_e', 'ing_h', 'ing_i', 'ing_k')
GROUP BY i.oid
UNION
SELECT DISTINCT i2.oid, GROUP_CONCAT(i2.ingrediens) har, '-' mangler
FROM ggxdgi i2
WHERE NOT EXISTS(SELECT oid, ingrediens FROM ggxdgi WHERE ingrediens NOT IN ('ing_a', 'ing_c', 'ing_e', 'ing_h', 'ing_i', 'ing_k') AND oid = i2.oid)
GROUP BY oid

og den gav dette resultat:


oid har                                mangler
2  ing_a                              ing_b,ing_g
4  ing_c,ing_h,ing_e                  ing_d,ing_f
6  ing_a,ing_k,ing_i,ing_h,ing_e,ing_c -
7  ing_a,ing_c,ing_e,ing_h            -

Det er ikke printet saa tydeligt, men for de to sidste opskrifter er 'mangler' = '-'

Jeg takker mange gange... du siger bare til hvis du skal have lidt flere point :)

Det lykkedes mig også at fikse den 'gamle' query, ved noget så simpelt som at rykke lidt rundt på hvor jeg før havde min kontrol af antal manglende ingredienser...


SELECT DISTINCT o.oid, o.navn, GROUP_CONCAT( i.ingrediens
SEPARATOR '\n' ) har, mi mangler, mico
FROM aa_opskrifter o
JOIN aa_ingredienser i ON o.oid = i.oid
LEFT JOIN (

SELECT DISTINCT COUNT( * ) mico, oid, GROUP_CONCAT( ingrediens
SEPARATOR '\n' ) mi
FROM aa_ingredienser
WHERE ingrediens NOT
IN (
'tyrkisk peber', 'rød sodavand', 'vodka', 'pøls', 'bandit', 'stefan', 'mel', 'vand'
)
GROUP BY oid
)m ON ( m.oid = o.oid
AND mico <= 3 )
WHERE i.ingrediens
IN (
'tyrkisk peber', 'rød sodavand', 'vodka', 'pøls', 'bandit', 'stefan', 'mel', 'vand'
)
GROUP BY i.oid
ORDER BY mico


"AND mico <= 3" flyttede jeg fra WHERE i hoved queryen, til ON() for JOIN...
utroligt at der ikke skulle mere til :P


Men igen - Mange tak for hjælpen :)