[PHP] Doctrine - NOT IN filter

[ZeroXT]

Er is een spel genaamd Mega Man in de database welke een many to many relatie heeft met genres. Dit spel heeft (o.a.) de genre avontuur welke gelijk is aan de genre met id 18.

Ik probeer een negatieve filter te maken waarbij gezocht kan worden op de titel van het spel in combinatie met de genre. Alleen komt hier altijd het spel naar voren terwijl ik dit niet verwacht.

Inmiddels een aantal variaties verder met allen hetzelfde resultaat:

PHP:

$queryBuilder = $this->createQueryBuilder('game');

//Variatie 1
$queryBuilder
  ->leftJoin('game.genres', 'genre', 'WITH')
  ->andWhere($queryBuilder->expr()->like('game.title', ':title'))
  ->andWhere('genre NOT IN(18)')
  ->setParameter('title', '%Mega Man%');

//Variatie 2
$queryBuilder
  ->leftJoin('game.genres', 'genre', 'WITH')
  ->andWhere($queryBuilder->expr()->like('game.title', ':title'))
  ->andWhere($queryBuilder->expr()->notIn('genre', [18]))
  ->setParameter('title', '%Mega Man%');

//Variatie 3
$queryBuilder
  ->leftJoin('game.genres', 'genre', 'WITH', 'genre NOT IN(:genres)')
  ->andWhere($queryBuilder->expr()->like('game.title', ':title'))
  ->andWhere($queryBuilder->expr()->notIn('genre', [18]))
  ->setParameter('title', '%Mega Man%')
  ->setParameter('genres', [18]);

//Hier komt altijd het spel Mega Man terug, terwijl ik juist geen resultaten verwacht.

Bij een positieve filter (dus WHERE IN() ipv WHERE NOT IN()) werkt dit perfect, maar op moment dat de NOT IN gebruikt wordt, krijg ik alsnog het spel terug alsof dit gelijk staat aan de IN variant.

Gebruik ik bijv. een IN met een genre id welke niet bestaat komt er, zoals verwacht, geen resultaat:

PHP:

$queryBuilder
  ->leftJoin('game.genres', 'genre', 'WITH')
  ->andWhere($queryBuilder->expr()->like('game.title', ':title'))
  ->andWhere('genre IN(9999999)')
  ->setParameter('title', '%Mega Man%');

Iemand enig idee waarom de NOT IN variant alsnog resultaat terug geeft?

Dit is de SQL wat ik probeer te bereiken:

SQL:

SELECT * FROM game 
WHERE game.title LIKE '%Mega Man%' AND game.id NOT IN(
    SELECT game_id FROM game_genre WHERE game_genre.genre_id IN(18)
);

[ Voor 20% gewijzigd door ZeroXT op 13-11-2021 22:22 ]

[Beyond]

Moet je dan niet ‘genre.id not in’ gebruiken?

[ Voor 15% gewijzigd door Beyond op 13-11-2021 22:25 ]

[ZeroXT]

Nee dat maakte niet uit. Beide gaven hetzelfde resultaat.

Inmiddels wat uitgeprobeerd en kom ik bij het volgende uit:

PHP:

$sub = $this->createQueryBuilder('g')
  ->leftJoin('g.genres', 'genre', 'WITH')
  ->andWhere('genre IN(:genres)')
  ->select('g.id')
  ->groupBy('g.id');

$queryBuilder = $this->createQueryBuilder('game')
  ->andWhere($queryBuilder->expr()->like('game.title', ':title'))
  ->setParameter('title', '%mega man Zero%')
  ->andWhere($queryBuilder->expr()->notIn('game.id', $sub->getDQL()))
  ->setParameter('genres', [18]);

Dit lijkt te werken. Maar is dit ook de meest efficiënte manier?

[ Voor 83% gewijzigd door ZeroXT op 15-11-2021 17:19 ]

[luukvr]

Je zou je positieve subquery op games eerst kunnen doen met een group_concat (zelf extra ; voor en na doen) op genres die het spel heeft (in de trans van 1;2;3;4), daar op zou je de negatieve filters kunnen toepassen door daar omheen SELECT * FROM (subquery) WHERE genres NOT like '%;1;%' AND genres NOT like '%;3;%'

Denk dat dit wel wat minder zwaar is dan een gigantische IN statement bouwen.

[MueR]

luukvr schreef op maandag 15 november 2021 @ 09:21:
Je zou je positieve subquery op games eerst kunnen doen met een group_concat (zelf extra ; voor en na doen) op genres die het spel heeft (in de trans van 1;2;3;4), daar op zou je de negatieve filters kunnen toepassen door daar omheen SELECT * FROM (subquery) WHERE genres NOT like '%;1;%' AND genres NOT like '%;3;%'

Denk dat dit wel wat minder zwaar is dan een gigantische IN statement bouwen.

Die LIKE gaat veel zwaarder zijn, want je skipt per definitie je index, daarnaast ga je string matching lopen doen. De subquery gaat over het selecteren van primary keys, op een foreign key, dus indexed, daar haalt een db z'n neus niet voor op tot je in de tientallen miljoenen komt.

[luukvr]

MueR schreef op maandag 15 november 2021 @ 11:47:
[...]

Die LIKE gaat veel zwaarder zijn, want je skipt per definitie je index, daarnaast ga je string matching lopen doen. De subquery gaat over het selecteren van primary keys, op een foreign key, dus indexed, daar haalt een db z'n neus niet voor op tot je in de tientallen miljoenen komt.

Ok misschien ook niet optimaal, 10 miljoen ids in een IN statement lijkt me ook niet heel lekker) maar je zou ook nog een left join kunnen doen waarbij je de geblackliste genres joined, wanneer het een join bevat ze kunnen uitfilteren via een having.

code:

SELECT game.*, COUNT(blacklist.id) as blacklisted 
FROM game 
INNER JOIN `game_genre` ON game.id=game_genre.game 
LEFT JOIN `genre` blacklist ON blacklist.id=game_genre.genre AND blacklist.id IN (1,3) 
GROUP BY game.id
HAVING blacklisted=0

[Voutloos]

@luukvr: Ipv die group by+having kan je ook gewoon WHERE blacklist.id IS NULL’ doen en dan krijg je enkel de rows waar je niet kan joinen.

ZeroXT schreef op zaterdag 13 november 2021 @ 21:42:
Iemand enig idee waarom de NOT IN variant alsnog resultaat terug geeft?

Met een join tussen game en genre heeft je tussenresultaat rows voor elke {game+specifieke genre waarde}. Als je dan een genre filtert, blijven games die ook andere genres hebben van harte over.

Dit is de SQL wat ik probeer te bereiken:

SQL:

SELECT * FROM game 
WHERE game.title LIKE '%Mega Man%' AND game.id NOT IN(
    SELECT game_id FROM game_genre WHERE game_genre.genre_id IN(18)
);

Dan is dat de query doe je moet bouwen, dus de suggestie van @Beyond en je latere poging daarvoor zijn juist.

Dus óf NOT IN(), óf joinen en controleren dat join niet gaat (Luukvr aanpak, maar zie mijn vereenvoudiging).

[luukvr]

Voutloos schreef op maandag 15 november 2021 @ 15:08:
@luukvr: Ipv die group by+having kan je ook gewoon WHERE blacklist.id IS NULL’ doen en dan krijg je enkel de rows waar je niet kan joinen.

Nee want dan komen spellen van het blacklisted genre via andere genres toch in je resultaat.

Idee is dat je via de inner join nog positieve genres via where kan blijven gebruiken

[ Voor 12% gewijzigd door luukvr op 15-11-2021 15:26 ]

[Voutloos]

Klopt @luukvr, het werkt enkel als het extra join is. Dus je hebt de normale join voor genre data, en dan nogmaals een join op genre met blacklisted alias. Dan werkt een where filter waarbij alles waar iets van blacklisted kolommen gevonden is.

Uiteindelijk subtiele variaties op de werkende denkwijze. Het illustreert wel hoe makkelijk de denkfout die in de varianten van de topicstart gemaakt is er in kan sluipen.

(Subjectief, maar de not in() aanpak wordt misschien sneller begrepen als je in sets denkt, dus zou daarom misschien mijn voorkeur hebben. )

[ Voor 27% gewijzigd door Voutloos op 16-11-2021 13:46 ]