[PHP/mySQL] Zoeken naar meerdere talen

[radem205]

Hey,

Ik heb een klein probleem en wel het volgende:

In de database heb ik een tabel "profieltalenkennis" waarin bijgehouden wordt welke talen kandidaten beheersen.
De tabel ziet er als volgt uit:

id	kandid	taal	niveau
1	297	2	4
2	297	3	3

En zo verder....

Zoals je ziet kan 1 kandidaat meerdere talen beheersen (is logisch). De cijfers onder "taal" correspondeert met de tabel "profieltalen".

Nu wil ik op talenkennis kunnen zoeken en wil ik ook meerdere talen tegelijk selecteren waarop gezocht kan worden. Dus als ik zoek op "Engels" en "Spaans" dan moet ik de kandidaten eruit krijgen die zowel Engels als Spaans spreken en niet Engels of Spaans.

Nu krijg ik met een left join in combinatie met "taal IN (2,5)" (2 en 5 zijn de taal id's) alle kandidaten die Engels of Spaans spreken, maar dit moet dus Engels en Spaans worden.

PHP:

<?
$sql = "SELECT
    blabla
FROM 
    kandidaten 
LEFT JOIN
    profieltalenkennis
ON
    profieltalenkennis.kandid = kandidaten.id
WHERE
profieltalenkennis.taal IN (2,5)
";
?>

Hoe krijg ik het voor elkaar om kandidaten er uit te krijgen die zowel Engels als Spaans spreken? Kan iemand mij in de goede richting helpen?

Alvast bedankt!

[Observer]

SQL:

SELECT 
   kandidaten.kandid, COUNT(kandidaten.kandid) AS talen 
FROM  
    kandidaten  
LEFT JOIN 
    profieltalenkennis 
ON 
    profieltalenkennis.kandid = kandidaten.id 
WHERE 
profieltalenkennis.taal IN (2,5)
GROUP BY kandidaten.kandid
HAVING talen >= 2

Dat zou alle kandidaten op moeten leveren die beide talen spreken. Als je meer dan 2 talen wilt weten zul je de 2 in de HAVING-clause moeten aanpassen naar het aantal talen.

[MueR]

Tip: Gebruik IN() zo min mogelijk. Het is een best trage functie/construct/whatever. Zeker bij een wat groter aantal zoek criteria.

[radem205]

Observer schreef op dinsdag 22 december 2009 @ 11:46:

SQL:

SELECT 
   kandidaten.kandid, COUNT(kandidaten.kandid) AS talen 
FROM  
    kandidaten  
LEFT JOIN 
    profieltalenkennis 
ON 
    profieltalenkennis.kandid = kandidaten.id 
WHERE 
profieltalenkennis.taal IN (2,5)
GROUP BY kandidaten.kandid
HAVING talen >= 2

Dat zou alle kandidaten op moeten leveren die beide talen spreken. Als je meer dan 2 talen wilt weten zul je de 2 in de HAVING-clause moeten aanpassen naar het aantal talen.

Bedankt, dit werkt uitstekend!

MueR schreef op dinsdag 22 december 2009 @ 11:58:
Tip: Gebruik IN() zo min mogelijk. Het is een best trage functie/construct/whatever. Zeker bij een wat groter aantal zoek criteria.

Wat is een betere methode dan? Gewoon allemaal OR gebruiken?

[ Voor 50% gewijzigd door radem205 op 22-12-2009 12:06 ]

[Woy]

MueR schreef op dinsdag 22 december 2009 @ 11:58:
Tip: Gebruik IN() zo min mogelijk. Het is een best trage functie/construct/whatever. Zeker bij een wat groter aantal zoek criteria.

Volgens mij valt dat wel mee als je hem op deze manier gaat gebruiken. Volgens mij is het niet zozeer dat IN() traag is, maar het is meer een probleem als je daar een correlated subquery voor gaat gebruiken dat je performance problemen kunt krijgen.

In dit geval zou je die conditie overigens ook in je join conditie op kunnen nemen, maar ik verwacht daar eigenlijk weinig winst mee. Maar je kunt natuurlijk altijd even naar het execution plan kijken.

[ Voor 17% gewijzigd door Woy op 22-12-2009 12:09 ]

[MueR]

Bij 2 of 3 argumenten in de IN zal je het niet merken nee. Bij een stuk of 10 begin je het te merken, bij een stuk of 20 ga je seconden wachten. Ik heb me daar onlangs nog over verbaasd. Ik moest 8 records hebben waarbij een forumid binnen $randomset lag. Duurde 5 seconden voor 8 records. 50 records ophalen en in PHP schiften deed het in een honderdste van de tijd.

[Woy]

MueR schreef op dinsdag 22 december 2009 @ 12:34:
Bij 2 of 3 argumenten in de IN zal je het niet merken nee. Bij een stuk of 10 begin je het te merken, bij een stuk of 20 ga je seconden wachten. Ik heb me daar onlangs nog over verbaasd. Ik moest 8 records hebben waarbij een forumid binnen $randomset lag. Duurde 5 seconden voor 8 records. 50 records ophalen en in PHP schiften deed het in een honderdste van de tijd.

Maar dan heb je het waarschijnlijk al over een bepaalde RDBMS ( MySQL? ). Dat het in MySQL misschien slecht geimplementeerd is, zegt natuurlijk niet iets generieks over alle RDBMS'en.

Uiteindelijk is het gewoon een filter operatie, die best efficiënt te doen is, mits er natuurlijk de juiste indexen zijn. En anders hoeft het ook niet langer te duren dan in bijvoorbeeld PHP.

[ Voor 12% gewijzigd door Woy op 22-12-2009 12:37 ]

[cariolive23]

MueR schreef op dinsdag 22 december 2009 @ 12:34:
Bij 2 of 3 argumenten in de IN zal je het niet merken nee. Bij een stuk of 10 begin je het te merken, bij een stuk of 20 ga je seconden wachten. Ik heb me daar onlangs nog over verbaasd. Ik moest 8 records hebben waarbij een forumid binnen $randomset lag. Duurde 5 seconden voor 8 records. 50 records ophalen en in PHP schiften deed het in een honderdste van de tijd.

Voortaan een betere database kiezen, dan heb je hier geen problemen mee en hoef je niet zelf het wiel uit te gaan vinden. Dat jouw database hier niet mee uit de voeten kan, zegt iets over jouw database, niet over DBMS-en in hun algemeenheid.

En over welke database heb je het eigenlijk? Weten we welke we niet moeten gebruiken

[kokx]

cariolive23 schreef op dinsdag 22 december 2009 @ 15:48:
[...]

Voortaan een betere database kiezen, dan heb je hier geen problemen mee en hoef je niet zelf het wiel uit te gaan vinden. Dat jouw database hier niet mee uit de voeten kan, zegt iets over jouw database, niet over DBMS-en in hun algemeenheid.

En over welke database heb je het eigenlijk? Weten we welke we niet moeten gebruiken

Volgesmij ging het om MySQL. Maar verder, waarschijnlijk zal er ook het een en ander aan de indexen gelegen hebben. Zeker bij de grotere tabellen word dat toch echt belangrijk .

[MueR]

cariolive23 schreef op dinsdag 22 december 2009 @ 15:48:
Voortaan een betere database kiezen, dan heb je hier geen problemen mee en hoef je niet zelf het wiel uit te gaan vinden. Dat jouw database hier niet mee uit de voeten kan, zegt iets over jouw database, niet over DBMS-en in hun algemeenheid.

Wat Woy dus al zei. Het is een MySQL specifiek probleem lijkt het. Het topic gaat over MySQL, dus zal mijn reactie daar ook vast over gaan In combinatie met PHP is dat vaak de enige keus, het gros van de webhosts ondersteunt standaard geen andere DBMSen. Misschien kan je iets minder met gestrekte benen inkomen?

[GlowMouse]

MueR schreef op dinsdag 22 december 2009 @ 12:34:
Bij 2 of 3 argumenten in de IN zal je het niet merken nee. Bij een stuk of 10 begin je het te merken, bij een stuk of 20 ga je seconden wachten. Ik heb me daar onlangs nog over verbaasd. Ik moest 8 records hebben waarbij een forumid binnen $randomset lag. Duurde 5 seconden voor 8 records. 50 records ophalen en in PHP schiften deed het in een honderdste van de tijd.

Onzin (voor MySQL iig); als je een SELECT fields FROM tbl WHERE indexed_column IN (...) hebt, en je hebt geen belachelijk laag geheugen gereserveerd voor indexen/rijen, dan is dat gewoon snel. Even snel als een OR trouwens.
Als voorbeeld kan ik de reacties op http://fotoboek.fok.nl/ geven, die allemaal zo worden opgehaald. Voor efficiënte paginering haal ik eerst de juiste reactieids uit een covering index (LIMIT is anders onwerkbaar traag bij grote offset), en daarna haal ik de reacties zelf met alle userinfo op met een WHERE reactieid IN(..).

[MueR]

Ben nog eens gaan kijken naar de specifieke query. Tegenwoordig schijnt de server er minder moeite mee te hebben (0.2s voor 8 records). Wat de oorzaak van de vertraging die ik zag dan was weet ik ook niet. Disk IO misschien. Het valt dus nog best mee.

[GlowMouse]

0.2s is nog traag voor een query. Bij 25 dingen in een IN, met joins op de PK van drie andere tabellen zit ik op 0.003s. Jouw probleem lijkt dus eerder i/o-bound, met name bij shared hosting heb je onvoldoende capaciteit; danwel door een slechte/slecht geconfigureerde server, danwel door queries van anderen die niet of slecht geoptimaliseerd zijn.

[MueR]

Dit waren er 150

[radem205]

Ik heb toch een probleem om een persoon te selecteren die beide talen spreekt (zie query hieronder):

code:

SELECT 

kandidaten.id, 
kandidaten.kandid, 
kandidaten.achternaam, 
kandidaten.tussenvoegsel, 
kandidaten.voorletters, 
kandidaten.voornaam, 
kandidaten.geslacht, 
kandidaten.emailprive, 
kandidaten.plaatsbaarheid, 
COUNT(profieltalenkennis.id) AS talen 

FROM 
kandidaten 

LEFT JOIN 
opleiding 
ON 
kandidaten.niveau = opleiding.id 

LEFT JOIN 
profielopleiding 
ON 
kandidaten.id = profielopleiding.kandid 

LEFT JOIN 
consultant 
ON 
kandidaten.consultant = consultant.id 

LEFT JOIN 
profieltalenkennis 
ON 
profieltalenkennis.kandid = kandidaten.id 

WHERE 
(kandidaten.plaatsbaarheid = '1' OR kandidaten.plaatsbaarheid = '2' OR kandidaten.plaatsbaarheid = '3') 
AND 
kandidaten.id > 0 
AND 
(profieltalenkennis.taal IN (1,13) AND profieltalenkennis.wijziging != 1)

GROUP BY profieltalenkennis.kandid
HAVING talen >= 2

Wanneer ik deze query in de database uitvoer retourneert ie maar liefst 48 rijen, terwijl er in dit geval maar één persoon zowel Japans (13) als Engels (1) spreekt.
De fout zit 'm in de COUNT binnen de SELECT, daar komen soms waarden uit groter dan 2, terwijl naar mijn inziens er maximaal 2 uit kan komen aangezien er 2 talen gematcht worden.

Weet iemand wat ik hier fout doe?

[RobIII]

Als je Aggregate functies gebruikt dien je sowieso een correcte GROUP BY te gebruiken. Zonder verder te hebben gekeken naar je query zie ik al meteen dat dat (1 van de dingen is wat) er aan schort.

[Woy]

Zonder het specifieke geval nog een keer bekeken te hebben. Kun je niet gewoon zoiets doen

code:

SELECT *
FROM Personen p
WHERE p.Id in ( Select Personen die taal 1 spreken )
AND p.Id in ( Select Personen die taal 2 spreken )

En anders zul je inderdaad naar de opmerking van RobIII moeten kijken, want je group by klopt in ieder geval niet

[ Voor 18% gewijzigd door Woy op 09-02-2010 10:18 ]

[radem205]

Ik heb even geprobeerd logisch na te denk en kwam ik uit op dat ik in de group by het volgende moet gebruiken:

code:

GROUP BY kandidaten.id 
HAVING talen >= 2

Zodat er groepjes worden gemaakt van alle verschillende kandidaten en daarna kan ik tellen hoeveel talen er matchen binnen één kandidaat, ECHTER dit werkt helaas nog niet. Hij retourneert nog steeds 2 of meer talen.

@Woy: Aangezien er op meerdere talen gezocht kan worden lijkt mij het handiger om het op te lossen met hetgeen wat ik heb (maar dan juist ).

[ Voor 20% gewijzigd door radem205 op 09-02-2010 10:34 ]

[RobIII]

radem205 schreef op dinsdag 09 februari 2010 @ 10:32:
Ik heb even geprobeerd logisch na te denk en kwam ik uit op dat ik in de group by het volgende moet gebruiken:

code:

GROUP BY kandidaten.id 
HAVING talen >= 2

Zodat er groepjes worden gemaakt van alle verschillende kandidaten en daarna kan ik tellen hoeveel talen er matchen binnen één kandidaat, ECHTER dit werkt helaas nog niet. Hij retourneert nog steeds 2 of meer talen.

Again: ik heb me niet verdiept in je query, maar een group-by dient sowieso alle velden te bevatten welke niet in aggregates zijn opgenomen. Dat is ook precies wat er staat uitgelegd bij het stukje over de Group By waar ik naar verwijs.

[ Voor 6% gewijzigd door RobIII op 09-02-2010 10:37 ]

[Woy]

radem205 schreef op dinsdag 09 februari 2010 @ 10:32:
Ik heb even geprobeerd logisch na te denk en kwam ik uit op dat ik in de group by het volgende moet gebruiken:

code:

GROUP BY kandidaten.id 
HAVING talen >= 2

Dat is nog steeds fout. Lees de link eens door die RobIII geeft wat betreft GROUP BY. In principe moet je gewoon groeperen op alle velden die niet door een aggregate functie gehaald worden. Dat MySql ook wat anders accepteert doet daar niks aan af.

offtopic:
* Woy slaps RobIII

[ Voor 4% gewijzigd door Woy op 09-02-2010 10:38 ]

[radem205]

Sorry voor mijn onwetendheid, maar ik begrijp nu dat alle velden die je onder SELECT hebt staan (met uitzondering van de aggregate velden) moet toevoegen in je GROUP BY, in mijn geval betekent dat dit (toch?):

code:

GROUP BY 
    kandidaten.id, 
    consultantnaam, 
    kandidaten.kandid, 
    kandidaten.achternaam, 
    kandidaten.tussenvoegsel, 
    kandidaten.voorletters, 
    kandidaten.voornaam, 
    kandidaten.geslacht, 
    kandidaten.emailprive, 
    kandidaten.plaatsbaarheid

Maar het werkt met deze GROUP BY nog steeds niet. Ok, het is fout dat mysql het slikt wanneer je niet alle velden in de GROUP BY zet, echter als je alleen groepen maakt van de verschillende kandidaten (dus GROUP BY kandidaten.id) dan kan je daar binnen toch die count uitvoeren. Bovenstaande GROUP BY geeft namelijk precies hetzelfde resultaat als het voorgaande.

Wanneer ik 1 taal selecteer dan krijg ik wel één persoon geretourneert die. in dit geval, Japans spreekt (13).

Edit: "talen" retourneert volgens mij steeds het volledig aantal talen die voor een specifieke kandidaat in de database staan, terwijl hij juist het aantal talen moet tellen die overeenkomen met de WHERE clause.

[ Voor 16% gewijzigd door radem205 op 09-02-2010 11:08 ]

[radem205]

Deze query werkt nu:

code:

SELECT 
    kandidaten.kandid, 
    kandidaten.achternaam, 
    kandidaten.tussenvoegsel, 
    kandidaten.voorletters, 
    kandidaten.voornaam, 
    kandidaten.geslacht, 
    kandidaten.emailprive, 
    kandidaten.plaatsbaarheid, 
    DATE_FORMAT(kandidaten.geboortedatum, '%d-%m-%Y') AS geboortedatum, 
    FLOOR(DATEDIFF(CURRENT_DATE(),kandidaten.geboortedatum )/365) AS leeftijd, 
    kandidaten.id, 
    COUNT(profieltalenkennis.kandid) AS talen 

FROM 
    kandidaten 
    
LEFT JOIN 
    _profieltalenkennis 
ON 
    profieltalenkennis.kandid = kandidaten.id 

WHERE 
(plaatsbaarheid = '1' OR plaatsbaarheid = '2' OR plaatsbaarheid = '3') AND kandidaten.id > 0 
AND 
((profieltalenkennis.taal IN (13,1) AND profieltalenkennis.wijziging != 1) 
OR 
(kandidaten.talenkennis LIKE '%Japans%' AND kandidaten.talenkennis LIKE '%Nederlands%')) 

GROUP BY 
    kandidaten.kandid, 
    kandidaten.achternaam, 
    kandidaten.tussenvoegsel, 
    kandidaten.voorletters, 
    kandidaten.voornaam, 
    kandidaten.geslacht, 
    _kandidaten.emailprive, 
    geboortedatum,
    leeftijd,
    kandidaten.plaatsbaarheid, 
    profieltalenkennis.kandid
     
HAVING 
    talen >= 2

Echter wanneer ik het volgende toevoeg:

code:

LEFT JOIN 
    profielopleiding 
ON 
    kandidaten.id = profielopleiding.kandid

en aan de GROUP BY "profielopleiding.kandid" toevoeg dan gaat het weer helemaal fout. Dan krijg ik bij "talen" een veel groter getal (dus niet 2, maar 3,4,5,6,etc.).
Hoe kan deze LEFT JOIN invloed hebben op de COUNT talen?

[ Voor 5% gewijzigd door radem205 op 09-02-2010 12:30 ]

[radem205]

Het is opgelost, alleen hoe kan ik er in onderstaande query voor zorgen dat er ook gezocht moet worden in het veld "kandidaten.talenkennis" naar een betreffende taal? Dus het moet voorkomen óf in de tabel "profieltalenkennis" of in het veld "kandidaten.talenkennis" (dus de INNER JOIN moet juist zijn of de taal moet in "kandidaten.talenkennis").

Dus deze " kandidaten.talenkennis LIKE '%".implode("%' AND kandidaten.talenkennis LIKE '%", $taaltekst)."%' " moet nog ergens toegevoegd worden alleen moet het samenwerken met de INNER JOIN.

SQL:

SELECT
    kandidaten.id,
    consultant.naam AS consultantnaam,
    kandidaten.kandid,
    kandidaten.achternaam,
    kandidaten.tussenvoegsel,
    kandidaten.voorletters,
    kandidaten.voornaam,
    kandidaten.geslacht,
    kandidaten.emailprive,
    kandidaten.plaatsbaarheid,
    DATE_FORMAT(kandidaten.geboortedatum, '%d-%m-%Y') AS geboortedatum,
    FLOOR(DATEDIFF(CURRENT_DATE(),kandidaten.geboortedatum )/365) AS leeftijd
FROM
    kandidaten
INNER JOIN
    ( SELECT profieltalenkennis.kandid, COUNT(*) FROM profieltalenkennis WHERE taal IN(5) AND wijziging != 1 GROUP BY kandid HAVING COUNT(*)=1 ) AS profielTaal
ON
    profielTaal.kandid = kandidaten.id
LEFT JOIN
    opleiding
ON
    kandidaten.niveau = opleiding.id
LEFT JOIN
    profielopleiding
ON
    kandidaten.id = profielopleiding.kandid
LEFT JOIN
    consultant
ON
    kandidaten.consultant = consultant.id
WHERE
    kandidaten.consultant = 3 AND (plaatsbaarheid = 1 OR plaatsbaarheid = 2 OR plaatsbaarheid = 3) AND kandidaten.id > 0

GROUP BY
    kandidaten.id,
    kandidaten.kandid,
    kandidaten.achternaam,
    kandidaten.tussenvoegsel,
    kandidaten.voorletters,
    kandidaten.voornaam,
    kandidaten.geslacht,
    kandidaten.emailprive,
    kandidaten.plaatsbaarheid

Edit: Sorry voor de posts achter elkaar.