20. august 2010 - 12:02Der er
23 kommentarer og 1 løsning
MySQL concat, returnere non-matches, ORDER BY efter mængden af disse osv.
Er det muligt at returnere de resultater der ikke matcher databasen og så dem der gør, kan man samtidigt tælle nonmatches, og arrangere resultaterne efter denne tælling?
SELECT cnavn,cnonmatch,cmatch FROM t1 GROUP_CONCAT( (SELECT cnavn,COUNT(*) AS cnmcnt FROM t1 WHERE cindhold NOT IN('ost','sovs','foo','bar','nisser')) SEPARATOR '\n') AS cnonmatch GROUP_CONCAT( (SELECT cnavn FROM t1 WHERE cindhold IN('ost','sovs','foo','bar','nisser')) SEPARATOR '\n') AS cmatch GROUP BY cnavn ORDER BY cnmcnt ASC
Jeg aner ikke om ovenstående fungerer, men hvis der er nogen der kan sige mig om det ville være muligt, eller om der er en anden/bedre måde at lave det på.
Umiddelbart vil jeg mene at
WHERE cindhold NOT IN('ost','sovs','foo','bar','nisser')
ikke vil retunere noget, fordi det ikke er en sammenligning mellem 2 database-elementer, er der nogen der kan be-eller-af-kræfte dette?
Fordele/ulemper. Hvis ovenstående kunne fungere, ville det kunne reducere diverse php scripts jeg har overvejet ganske betydeligt. Men jeg ved også at GROUP_CONCAT er begrændset til at outputte 1024 tegn, medmindre man har adgang til at rode med group_concat_max_len og max_allowed_packet iflg. http://dev.mysql.com/doc/refman/5.6/en/group-by-functions.html
Med kunstig intelligens skaber HP’s nye OmniBook X 14 en unik og skræddersyet brugeroplevelse målrettet dem, der ønsker høj ydeevne og intelligente funktioner
SELECT cnavn AS cna,cnonmatch,cmatch FROM t1 GROUP_CONCAT( (SELECT cindhold,COUNT(*) AS cnmcnt FROM t1 WHERE cnavn = cna AND cindhold NOT IN('ost','sovs','foo','bar','nisser')) SEPARATOR '\n') AS cnonmatch GROUP_CONCAT( (SELECT cindhold FROM t1 WHERE cnavn = cna AND cindhold IN('ost','sovs','foo','bar','nisser')) SEPARATOR '\n') AS cmatch GROUP BY cnavn ORDER BY cnmcnt ASC
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 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.
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:
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
"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 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.
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:
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
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 -
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 :)
Synes godt om
Ny brugerNybegynder
Din løsning...
Tilladte BB-code-tags: [b]fed[/b] [i]kursiv[/i] [u]understreget[/u] Web- og emailadresser omdannes automatisk til links. Der sættes "nofollow" på alle links.