Why I must use object in doctrine relation?

Question

Why I must use object in doctrine relation?

For example I have two Entity - Category and Post. From jquery I receice in controller many ids:

$categories = array(3, 5, 2);

And if I want get object then I must first get objects:

$cat = array();
foreach ($categories as $category) {
   $cat[] = $this->getDoctrine()
        ->getRepository('AppBundle:Category')
        ->find($category);
}

And only now I can find my Posts:

$repository = $this->getDoctrine()
                ->getRepository('AppBundle:Post');

            $query = $repository->createQueryBuilder('p');

$query->andWhere('p.category IN (:cat)')
              ->setParameter('cat', $cat);

$posts  = $query->getQuery()->getResult();

Why I can't use simply ids categories in second query? Why I must pass object to query with relation? Why query builder can't see category_id instead of category?

It charged the database and increases page load time.

Answer 1

It won't see category_id but it will accept a plain list of ids:

    $ids = array();
    foreach($catIds as $catId)
    {
        $ids[] = $catId['category_id'];
    }
    // So $ids look like: array(3, 5, 2)

    $qb->setParameter('cat', $ids);  

You were very close. Just had to plug in your $categories.

Answer 2

You can find your Posts directly by Category ID either via findBy() method:

$repository = $this->getDoctrine()->getRepository('AppBundle:Post');
$posts = $repository->findBy(['category' => $categories]);

or via QueryBuilder:

$repository = $this->getDoctrine()->getRepository('AppBundle:Post');
$query = $repository->createQueryBuilder('p')
    ->where('p.category IN (:categories)')
    ->setParameter('categories', $categories);
$posts = $query->getQuery()->getResult();

Where $categories is an array of your categories IDs.

Also you should use where() instead of andWhere() for your first where case in your query.

Answer 3

You can change your query builder to join to the category and use the category id like...

$this
    ->getDoctrine()
    ->getRepository('AppBundle:Post')
    ->createQueryBuilder('p')
    // Join to the category object and set the alias to "c"
    ->join('p.category', 'c')
    // where category id is in list of ids
    ->andWhere('c.id IN (:categoryIds)')
    ->setParameter('categoryIds', $categoryIds)
    ->getQuery()
    ->getResult();