在Doctrine2中检索关系时如何添加额外的WHERE子句

Question

I have two entities Post and Comment.
Structure:

Post: id title body

Comment: id post_id body active

class Post
{
/**
 * @ORM\Column(name="id", type="integer")
 * @ORM\Id
 * @ORM\GeneratedValue(strategy="AUTO")
 */
private $id;

/**
 * @ORM\Column(name="title", type="string", length=255)
 */
private $title;

/**
 * @ORM\Column(name="body", type="text")
 */
private $body;

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

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

/**
 * @ORM\Column(name="body", type="text")
 */
private $body;

/**
 * @ORM\ManyToOne(
 *      targetEntity="Post",
 *      inversedBy="comments"
 * )
 * @ORM\JoinColumn(
 *      name="post_id",
 *      referencedColumnName="id"
 * )
 */
private $post;

As a result when I want to get all comments for a post I use $post->getComments() and it works. How I can add extra Where clauses into this relationship if I want to get only posts with active = 1. I know that I can do it by DQL or queryBuilder but I want to know how I can do it by mapping

Answer 1

I think the cleanest way to retrieve only active comments is to use Doctrine's Criteria object in the getComments method of your Post entity

use Doctrine\Common\Collections\Criteria;

and

public function getComments()
{
    $criteria = Criteria::create();
    $criteria->where(Criteria::expr()->eq('active', 1));        
    return $this->comments->matching($criteria);
}

Edit

If you want to prevent multiple queries each time you retrieve the active comments, you'll need to store them in a local variable. Instead of modifying getComments, you could add $active_comments and getActiveComments, which will populate $active_comments and only query the db if $active_comments is false.

class Post {

    private $active_comments;

    public function getActiveComments()
    {
        if(!$this->active_comments) {        
            $criteria = Criteria::create();
            $criteria->where(Criteria::expr()->eq('active', 1));        
            $this->active_comments = $this->comments->matching($criteria);
        }

        return $this->active_comments;
    }