带有参数数组的SQL请求

Question

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 1

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'])));