带有参数数组的SQL请求

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

查看全部
dongyuqie4322
dongyuqie4322
2017/06/20 17:23
  • php
  • mysql
  • symfony
  • sql
  • 点赞
  • 收藏
  • 回答
    私信
满意答案
查看全部

1个回复