8
\$\begingroup\$

I'm making a section that shows a list of blogs and how many comments it has without loading any comment data. My Blog entity is as follows:

/**
 * @ORM\Entity
 * @ORM\Table(name="blogs")
 */
class Blog
{   
    /**
     * @ORM\Id
     * @ORM\Column(type="integer")
     * @ORM\GeneratedValue(strategy="AUTO")
     */
    protected $id;

   // This field is aggregated. Not in DB table
    protected $totalComments = 0;

    /**
     * @ORM\Column(type="string")
     */
    protected $title;

     /**
     * @ORM\OneToMany(targetEntity="Comment", mappedBy="blog")
     */
    protected $comments;

    // More attributes

    public function __construct(){
        $this->comments = new ArrayCollection();
    }

    // Setters and Getters
}

and my Comment Entity:

class Comment
{
    /**
     * @ORM\Id
     * @ORM\Column(type="integer")
     * @ORM\GeneratedValue(strategy="AUTO")
     */
    protected $id;

    /**
     * @ORM\ManyToOne(targetEntity="Blog", inversedBy="comments")
     * @ORM\JoinColumn(name="blog_id", referencedColumnName="id")
     */
    protected $blog;

   // more attributes

    public function __construct(){     }

    // Setter and Getters
}

They have a oneToMany relationship. 1 Blog has N comments.

My next step is convert this query to the Doctrine2 World:

'SELECT b.*, COUNT( b.id ) AS totalComments FROM  `blogs` b LEFT JOIN comments c ON b.id = c.blog_id GROUP BY b.id'

That was an easy task:

function getLatestBlogs(){  
        $qb = $this->createQueryBuilder('b')
        ->select('b, c')
        ->addSelect('count(b.id) as totalComments')
        ->leftJoin('b.comments', 'c')
        ->groupBy('b.id');

        $result = $qb->getQuery()->getResult();

        foreach($qResult as $row){ //tweaking original result
            $row[0]->setTotalComments($row['totalComments']);
            $list[] = $row[0];
        }
        return $list;
    }

In the foreach loop is where I fix my fetched result because doctrine's getResult was returning an array of arrays like this:

// var_dump($result)
array (size=8)
  0 => 
    array(2) =>
      0 =>  
          object Blog
          'id' => int 330
          'title' => string 'title blog'
          // ... more attributes

      "totalComments" => int 5

  1 => ...
  );

Is there any fashion way that I'm missing ? I've really dig in to depth and couldn't find anything that fix this situation.

\$\endgroup\$
1
\$\begingroup\$

If you wanna just show them, just use

count($blog->getComments()) or blog.comments|length in template, or something similar.

\$\endgroup\$
  • 1
    \$\begingroup\$ Actually that was my first approach, but then I realized that I was getting an extra query for each blog. That's because of doctrine's lazy load \$\endgroup\$ – Mollo Dec 18 '14 at 17:03
0
\$\begingroup\$

In addition to previous answer

You can create method in repository like getBlogsWithJoins:

return $this->createQueryBuilder('b')
    ->select('b')
    ->join('b.comments')
    ->getQuery()
    ->getResult();

And then doctrine would load all the comments related to blog post in eager way.

\$\endgroup\$

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.