Ik probeer een lading films uit mijn SQLite database te halen, compleet met hun categorieën (middels koppeltabel) erbij. Op zich te doen, ware het niet dat ik alle films met 1 bepaalde categorie wil hebben. Hier botsen het 'meerdere categorieën per film ophalen' en 'alle films in bepaalde categorie' met elkaar.
edit: het probleem is nu dat hij alleen dat het resultaat van SERIALIZE alleen de categorie waar ik op zocht bevat, en niet de categorieën die allemaal bij de gevonden film horen.
Dit is mijn query so far:
SELECT
Films.*,
SERIALIZE(Categories.label) as categories
FROM
Films
LEFT JOIN
FilmCategories
ON FilmCategories.filmId = Films.id
LEFT JOIN
Categories
ON Categories.id = FilmCategories.categoryId
WHERE
Categories.label LIKE '%s'
GROUP BY
Films.id
De structuur van de database, voor zover van belang:
Films
- id
Categories
- id
- label
FilmCategories (koppeltabel)
- filmId
- categoryId
Verder, de functie SERIALIZE werkt een beetje vergelijkbaar met COUNT in combinatie met de GROUP BY. Hij verzamelt alle individuele categorieën die matchen (zoals COUNT alle matches telt) en geeft ze achteraf terug als een [php]serialize[/php]d array. Je zou hem voor testdoeleinden dus kunnen vervangen door COUNT :)
Dat komt omdat SERIALIZE mijn eigen functie is. Die heb ik erbij gemaakt zodat ik een 1-op-meer relatie kan fetchen zonder dat de 1 in de 1-op-meer relatie verdubbeld wordt in de uitvoer.
Momenteel is dit bijvoorbeeld de uitvoer. Let op 'categories'.
id title categories
3 Blaat a:1:{i:0;s:5:"Actie";}
1 Return of the Blaatschaap a:1:{i:0;s:5:"Actie";}
of met COUNT ipv SERIALIZE (om het even simpel te houden)
id title categories
3 Blaat 1
1 Return of the Blaatschaap 1
terwijl ik meerdere categories per film zou willen hebben:
id title categories
3 Blaat 4
1 Return of the Blaatschaap 6
Heb net even wat lopen proberen en ik denk dat je met het gebruik van een subquery er wel uit komt.
Met behulp van de subquery laat je het aantal categorieen tellen waar een film toe behoort en dat aantal neem je vervolgens mee in de hoofdquery.
Het zal iets worden als:
SELECT
f.*,
c.label,
(SELECT
COUNT(id)
FROM filmcategorie
WHERE film_id = f.id
) AS aantal_cat
FROM
films AS f
LEFT JOIN
filmcategorie AS fc
ON
fc.film_id = f.id
LEFT JOIN
categorieen AS c
ON
c.id = fc.cat_id
WHERE
c.label = 'actie'
De namen van de tabellen zullen iets afwijken, maar het lijkt me zo wel duidelijk...
ps. Misschien even ter illustratie de brongegevens en queries die ik gebruikt heb:
mysql> SELECT * FROM films; SELECT * FROM categorieen; SELECT * FROM filmcategorie;
+----+---------------------------+
| id | naam |
+----+---------------------------+
| 1 | blaat |
| 2 | return of the blaatschaap |
| 3 | blaatieschaapie |
+----+---------------------------+
3 rows in set (0.00 sec)
+----+----------+
| id | label |
+----+----------+
| 1 | actie |
| 2 | avontuur |
| 3 | drama |
| 4 | thriller |
+----+----------+
4 rows in set (0.00 sec)
+----+---------+--------+
| id | film_id | cat_id |
+----+---------+--------+
| 1 | 1 | 1 |
| 2 | 1 | 3 |
| 3 | 1 | 4 |
| 4 | 2 | 3 |
| 5 | 3 | 1 |
| 6 | 3 | 2 |
+----+---------+--------+
6 rows in set (0.00 sec)
mysql> SELECT f.*, c.label, (SELECT COUNT(id) FROM filmcategorie WHERE film_id = f.id ) AS aantal_cat
-> FROM films f
-> LEFT JOIN filmcategorie fc ON fc.film_id = f.id
-> LEFT JOIN categorieen c ON c.id = fc.cat_id
-> WHERE c.label = 'actie';
+----+-----------------+-------+------------+
| id | naam | label | aantal_cat |
+----+-----------------+-------+------------+
| 1 | blaat | actie | 3 |
| 3 | blaatieschaapie | actie | 2 |
+----+-----------------+-------+------------+
2 rows in set (0.00 sec)
Helaas, ik kreeg de subquery binnen het SELECT onderdeel niet werkend. Blijkbaar kan je in SQLite niet binnen subqueries verwijzen naar kolommen uit de bovenliggende query.
Ik heb het nu opgelost met een view. Die view bevat alles van Films, samen met het resultaat van SERIALIZE bij de bijbehorende film. Dit werkt tot nu toe verder prima.
