CODEWITHSUNDEEP
symfonydoctrine-ormquery-builder
Kincsem
Kincsem

Reputation: 137

Symfony2 - How can I simplify this querybuilder?

I have a working queryBuilder that gets posts by category and excludes the one being shown and sets a max limit.

Question: How can I simplify this queryBuilder?

In the query, I am pretty sure I don't need to join the 2 tables (category/post connected with a OneToMany/ManyToOne relationship) and am setting $category in the controller, is there a better way of refactoring this?

queryBuilder

public function getRelatedPosts($exceptPost, $limit, $category)
{
return $this
->createQueryBuilder('post') 
->leftJoin('post.category','category') 
->where('post != :exceptPost') 
->setParameter('exceptPost', $exceptPost) 
->andWhere('category = :category') 
->setParameter('category', $category) 
->orderBy('post.createdAt', 'DESC') 
->setMaxResults($limit)
->getQuery()
->execute();
}

controller

public function showAction($slug)
{
    $post = $this->getDoctrine()->getRepository('AcmeDemoBundle:Post')
        ->findOneBy(array(
            'slug' => $slug
        ));

    if (null === $post) {
        throw $this->createNotFoundException('Post was not found');
    }

    $category = $post->getCategory(); 

    $posts = $this->getDoctrine()->getRepository('AcmeDemoBundle:Post')
        ->getRelatedPosts($post, 4, $category);

    return array(
        'post'  => $post,
        'posts' => $posts
   );
}

updated queryBuilder

public function getRelatedPosts($exceptPost, $limit, $category)
{
    return $this
        ->createQueryBuilder('post')
        ->where('post != :exceptPost')
        ->andWhere('post.category = :category')
        ->setParameter('exceptPost', $exceptPost)
        ->setParameter('category', $category)
        ->orderBy('post.createdAt', 'DESC')
        ->setMaxResults($limit)

        ->getQuery()
        ->execute();
}

Upvotes: 0

Views: 111

Answers (1)

Javad
Javad

Reputation: 4397

I am not sure if this is what you are looking for but you may want something like this:
Controller

public function showAction($slug)
{
   $post = $this->getDoctrine()->getRepository('AcmeDemoBundle:Post')
      ->findOneBy(array(
        'slug' => $slug
   ));

   if (null === $post) {
      throw $this->createNotFoundException('Post was not found');
   }

   $posts = $this->getDoctrine()->getRepository('AcmeDemoBundle:Post')
      ->getRelatedPosts($post, 4);

   return array(
      'post'  => $post,
      'posts' => $posts
   );
}

Repository

public function getRelatedPosts($exceptPost, $limit)
{
   return $this
     ->createQueryBuilder('post')
     ->where('post.id != :exceptPost')
     ->andWhere('post.category = :category')
     ->setParameter('exceptPost', $exceptPost->getId())
     ->setParameter('category', $exceptPost->getCategory()->getId())
     ->orderBy('post.createdAt', 'DESC')
     ->setMaxResults($limit)
     ->getQuery()
     ->getResult();
}

You can also build this in a join query of post table with itself or SELECT ... IN (...); if you are interested

Upvotes: 1

Related Questions