Doctrine2 Association Mapping with Conditions

Doctrine 2, association mapping with conditions

You can use a join instead of a where condition to filter your collection on the database level.

Your query would then look like this:

$articles = $em->createQueryBuilder()
            ->from(Article::class, 'article')
            ->leftJoin('article.comments', 'comments', 'WITH', 'comments.approved = :ap')
            ->addSelect('article')
            ->addSelect('comments')
            ->setParameter('ap', true)
            ->getQuery()->getResult();

Since this is a left join, it will return all articles, even if they do not have any approved comment.

Doctrine2 association mapping with conditions

You can use the Criteria API to filter the collection:

<?php

use Doctrine\Common\Collections\Criteria;

class Article
{

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

    public function getComments($showPending = false)
    {
        $criteria = Criteria::create();
        if ($showPending !== true) {
            $criteria->where(Criteria::expr()->eq('approved', true));
        }
        return $this->comments->matching($criteria);
    }

}

This is especially nice, because Doctrine is smart enough to only go to the database if the collection hasn't already been loaded.

Using both basic AND association mapping for one field with Doctrine?

Well, some more testing, using inconsistent data entries in my entity seem to clearly demonstrate that such usage isn't intended.

Whild my code wouldn't allow this, if I do save my entity with groupId = 1 and Group = (group Object with ID 2), I get an alternance of values for the saved value in the "parent" column of my database.

I guess this is due to Doctrine skipping the "useless" update, and proceeding to the one which effectively changes the data value.
Since both values are different, the skipped update alternantes.

If my assumptions are right, Doctrine would better pop an error in such a mapping case. (as it does if you map one column to 2 fields).
Maybe I'll file a suggestion/issue, if this seem relevant.

How to get doctrine2 entity one to many entities that are set active

You should use the matching method of your collection. If your collection is not loaded, it will add filters to the SQL query to only load what you need. If your collection is already loaded, it will filter the PHP array.

use Doctrine\Common\Collections\Criteria;

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

More informations here: http://doctrine-orm.readthedocs.org/projects/doctrine-orm/en/latest/reference/working-with-associations.html#filtering-collections

Regards

Symfony where clause on the entity level

Doctrine does not support conditional associations in mapping. To achive this behavior you can use Criteria API in the entity methods. And yes, in this case all data will be fetched from DB before applying condition.

But Doctrine (>=2.2) supports Filters. This feature allows to add some SQL to the conditional clauses of all queries. Soft-deletes can be implemented through this feature.

The DoctrineExtensions library already has this functionality (SoftDeletable, based on Filters API).

Also, many don't recommend to use soft-deletes (1, 2).

---

Related Topics