PFZ.nl - PHP Community: Many to many relation met dubbelewhere clause - Databases - Forum - PFZ.nl - PHP Community

Ik heb een many to many relationship tussen books en authors, immers een boek kan meerdere auteurs hebben en een auteur meerdere boeken. De koppeltabel heet authors_books.

Het is me al gelukt om te zoeken op boeken die zowel door auteur 'Jan Wolkers' als door 'Herman Koch' geschreven zijn. Onderstaande levert dan ook een goed resultaat op.

1 2 3 4 5 6 7 8

SELECT books.id, books.title FROM books RIGHT JOIN authors_books AB1 ON AB1.book_id = books.id LEFT JOIN authors AS A1 ON A1.id = AB1.author_id RIGHT JOIN authors_books AB2 ON AB2.book_id = books.id LEFT JOIN authors AS A2 ON A2.id = AB2.author_id WHERE A1.name = 'Jan Wolkers' AND A2.name ='Herman Koch'

Nu ben ik echter op zoek naar alle boeken die wel door 'Jan Wolkers' zijn geschreven, maar waar 'Herman Koch' niet aan mee heeft geschreven. Hoe krijg ik dit voor elkaar? Onderstaande werkt niet (is ook logisch).

1 2 3 4 5 6 7 8

SELECT books.id, books.title FROM books RIGHT JOIN authors_books AB1 ON AB1.book_id = books.id LEFT JOIN authors AS A1 ON A1.id = AB1.author_id RIGHT JOIN authors_books AB2 ON AB2.book_id = books.id LEFT JOIN authors AS A2 ON A2.id = AB2.author_id WHERE A1.name = 'Jan Wolkers' AND A2.name !='Herman Koch'

Zoals meestal in SQL geldt ook hier: als de oplossing erg ingewikkeld lijkt, dan doe je het waarschijnlijk fout.
In die geval wordt de query een waar drama als je het over vier schijvers hebt, met zes joins... neuh.


Bekijk het eens anders; als een boek ie geschreven door Wolkers en Koch, dan is er één koppeling voor Wolkers en één voor Koch, dus er zijn in totaal twee koppelingen waar schrijver=wolkers of koch.

Als je boeken zoekt die niet door koch geschreven zijn dan is het aantal koppelingen waar schijver=koch gelijk aan nul.

Oh ik vond de oplossing van de eerste query eigenlijk nog niet zo ingewikkeld ;-) Maar je hebt gelijk dat ie steeds ingewikkelder wordt naarmate het aantal auteurs hoger wordt.

Maar verder ontgaat jouw oplossing mij volledig... Ik gok dat je iets met WHERE COUNT(ratings.id) = 2 wil doen voor de eerste query, maar hoe je het dan verder op zou lossen?

En voor de tweede moet het aantal koppelingen met koch 0 zijn idd, maar met wolters 1.

Zou je iets meer kunnen toelichten?

Quote

Maar verder ontgaat jouw oplossing mij volledig... Ik gok dat je iets met WHERE COUNT(ratings.id) = 2 wil doen voor de eerste query, maar hoe je het dan verder op zou lossen?

Als een boek twee schrijvers heeft dan staan er twee records voor dat boek in authors_books. Dus als Wolkers en Koch samen een boek hebben geschreven dan kun je dat boek vinden met iets als:

SELECT book_id
FROM authors_books
WHERE
author_id IN (wolkers, koch)
GROUP BY bool_id
HAVING COUNT(*)=2;

Om het flexibel te houden kun je iets maken als:

SELECT
book_id
, COUNT(*) AS num_authors
, SUM(CAST(author_id IN (wolkers) AS INTEGER)) AS matching_authors
, SUM(CAST(author_id IN (koch) AS INTEGER)) AS unwanted_authors
FROM authors_books
GROUP BY book_id
HAVING
SUM(CAST(author_id IN (wolkers) AS INTEGER)) = 1
AND
SUM(CAST(author_id IN (koch) AS INTEGER)) = 0

Dat geeft alle boeken waarvoor wolkers één keer is gevonden, en koch nul keer. Zo zou je dus ook kunnen zoeken naar een boek dat door wolkers en koch is geschreven (matching_authors=2) en niet door pietersma (unwanted_authors=0)

ps in de select staat de matching en unwanted alleen om de waarden zichtbaar te maken tijdens het debuggen/ontwerpen van de query, in productie hoeft dat alleen in de HAVING te staan.

Dank! De eerste snap ik. De tweede vind ik wat moeilijker om te begrijpen. Hij werkt overigens bij mij ook zonder de CAST, maar dat terzijde.

Onderstaande query werkt. Ik snap alleen niet waarom.

1 2 3 4 5 6 7

SELECT book_id FROM authors_books GROUP BY book_id HAVING SUM(author_id IN (SELECT id FROM authors WHERE name = 'wolters')) = 1 AND SUM(author_id IN (SELECT id FROM authors WHERE name = 'koch')) = 0

1. Wat doet de SUM? Wat ik denk:

(author_id IN (SELECT id FROM authors WHERE name = 'wolters')) returns true (wat gelijk staat aan 1)

2. Maar dan zou het ook zonder SUM moeten werken en dat is dus niet zo. Of heb je altijd een SUM of COUNT nodig in de HAVING functie?

3. Als ik nu meerdere auteurs heb waaraan ie moet voldoen kan dat dus het beste zo?

1 2 3 4 5 6 7

SELECT book_id FROM authors_books GROUP BY book_id HAVING SUM(author_id IN (SELECT id FROM authors WHERE name = 'wolters' OR name='pauw')) = 2 AND SUM(author_id IN (SELECT id FROM authors WHERE name = 'koch')) = 0

Quote

Hij werkt overigens bij mij ook zonder de CAST, maar dat terzijde.

Officieel niet, je kunt true en false niet bij elkaar optellen.

Quote

1. Wat doet de SUM? Wat ik denk:

Optellen. Ik selecteer per record of het authorid in de verzameling zit en zoja dan telt SUM er 1 bij op.

Quote

2. Maar dan zou het ook zonder SUM moeten werken en dat is dus niet zo. Of heb je altijd een SUM of COUNT nodig in de HAVING functie?

Je wilt het aantal per boek, dus een aggregaat en dus GROUP BY en SUM en COUNT. En omdat er moet worden gefilterd op basis van de totalen per boek moet er een HAVING bij.


3. Het kan, maar of het efficient is is een tweede, gebruik EXPLAIN.