Doctrine Constraint on a Many-to-Many Relation

Question

We have an Entity 'User' that has a Many-to-Many Relation to different 'Types'. The MySQL Relation is handled via a Cross Table.

class User {

    /**
     * @ORM\ManyToMany(targetEntity="App\Entity\Type")
     * @ORM\JoinTable(name="user_type",
     *      joinColumns={@ORM\JoinColumn(name="user_id", referencedColumnName="id")},
     *      inverseJoinColumns={@ORM\JoinColumn(name="type_id", referencedColumnName="id")}
     * )
     */
    protected $types;

}



class Type {

     /**
     * @ORM\ManyToMany(targetEntity="App\Entity\User")
     * @ORM\JoinTable(name="user_type",
     *      joinColumns={@ORM\JoinColumn(name="type_id", referencedColumnName="id")},
     *      inverseJoinColumns={@ORM\JoinColumn(name="user_id", referencedColumnName="id")}
     * )
     */
    protected $users;

     /**
     * @ORM\Column(type="datetime")
     */
    protected $publishedAt;

}

How can I limit the Responses in this many-to-many relation to only show Types that have already been published?

That is the factual SQL should include a WHERE that filters the corresponding items that have not been published yet. Can I do that with an annotation or something like that?

I know I can do this by filtering the returned collection. But is there anything more efficient than that?

Answer 1

This question is kind of a douplicate. It has been answered here: php - Doctrine2 association mapping with conditions - Stack Overflow

One of the comments tells, that this results in an error for Many-to-Many Relations. As of Doctrine 2.5 this no longer is the case.

So what you can do in doctrine is hand over a query condition when you request the entities of the relation:

So you do not change the Annotation, but the getter for the Entity:

class User {

    /**
     * @ORM\ManyToMany(targetEntity="App\Entity\Type")
     * @ORM\JoinTable(name="user_type",
     *      joinColumns={@ORM\JoinColumn(name="user_id", referencedColumnName="id")},
     *      inverseJoinColumns={@ORM\JoinColumn(name="type_id", referencedColumnName="id")}
     * )
     */
    protected $types;

    public function getTypes()
    {
        $criteria = Criteria::create()->where(Criteria::expr()->lte('publishedAt', date('Y-m-d')));
        return $this->types->matching($criteria);
    }


}

This will result in a Lazy-Fetch (depending on your settings) of the required items. All the doctrine magic still works, like caching and the like. So the collection will only be fetched, if it has not been fetched...