Doctrine2 + join conditions

First of all I want to say big thanks to Doctrine developers, guys you are rock! My problem is the following:

I have three tables:

person (id, name, isActive)
email (id, address, isActive)
personEmailRel(id, personId, emaiId, isActive)

And I want to get list of emails by person:

/**
 * Unidirectional - Many persons have many emails
 *
 * @ManyToMany(targetEntity="Addres开发者_StackOverflows_Model_Email")
 * @JoinTable(name="personEmailRel",
 *   joinColumns={@JoinColumn(name="personId", referencedColumnName="id")},
 *   inverseJoinColumns={@JoinColumn(name="emailId", referencedColumnName="id")}
 * )
 */
private $_emails;

public function __construct()
{
    $this->_emails = new Collections\ArrayCollection();
}

public function getEmails()
{
    return $this->_emails;
}

Works fine. But the problem is that I also want to set addition condition in join clause isActive=1. How to solve that in Doctrine2? Thanks.

You don't work with ManyToMany but create a third entity PersonEmail.

I find your database schema highly questionable though. Why can an email have many persons? Shouldn't a OneToMany relation suffice in this case? I dont see the benefit of the join table.

Doctrine doesn't support conditions in associations, but there are several ways to solve this:

Create a repository method that queries the related entities you want. See Query and QueryBuilder.

Use the Collection Criteria API to filter inactive entities from the collection in a getter (in your entity). If the association is marked as "EXTRA_LAZY", optimized queries will be used in stead of retrieving the entire collection. This is very useful for large collections.

Use the Filter API to filter inactive entities at a DB level. This is quite useful when you almost always want to hide inactive (or deleted, etc) entities.