Victor Garcia Victor Garcia - 2 months ago 5
MySQL Question

SQL request with an array of arguments

I have a problem with a SQL(dql) request,
I have a table with Books(Media), a table with categories, and a table who associate the two: CategoryAffiliation (a book can have multiple categories)

In the Client side, i can choose to search for books by checking multiple categories. Then the client send a array of categories to the server and he must return the books who have at least one of those categories.

$books = [];
if (isset($data['categories'])) {
foreach ($data['categories'] as $key => $value){
$query = $em->createQuery("SELECT m
FROM AppBundle:Media m, AppBundle:Category c, AppBundle:CategoryAffiliation mc
WHERE m.idMedia = mc.idMedia
AND mc.idCategory = :key
GROUP by m.idMedia"
)->setParameter('key',$key);
$result = $query->getResult();
foreach ($result as $book) {
array_push($books, $book);
}
}
}


This is my solution for now but this is not great, i can't ORDER BY the result..
I receive an array of duplicates books, and then in the client side I merge them to delete the duplicates.

I am searching a way to have a single request to fetch all the data at once

Thanks

Answer Source

I don't have access to my stuff to test it but this could do it. I'm just not sure how setParameter will handle the fact that I put ' inside the string. I think that you will need to get the raw information. I can't remember how symfony and doctrine manage that, it's been too long ;)

If you can't find an other way, you could build it in the string directly... which I don't really recommend.

$query = $em->createQuery("SELECT m
                           FROM AppBundle:Media m, AppBundle:Category c, AppBundle:CategoryAffiliation mc
                           WHERE m.idMedia = mc.idMedia
                                 AND mc.idCategory IN (:key)
                           GROUP by m.idMedia"
        )->setParameter('key',implode("','",array_keys($data['categories'])));