(Doctrine) Select where array collection is not empty?

Question

I have a class Post:

/**
 * @ORM\Entity(repositoryClass="App\Repository\PostRepository")
 */
class Post
{
    const TYPE_TEXT   = 1;
    const TYPE_PHOTOS = 2;
    const TYPE_VIDEO  = 3;

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

    and other properties, methods, etc...

And I want to bring back only the posts that have any photos.

I have a DQL query like:

    $qb = $this->createQueryBuilder('p');
    $qb->select('p, postPhotos, etc...')
        ->leftJoin('p.photos', 'postPhotos')
        ->leftJoin('p.videos', 'postVideos')
        etc...

    if ($mediaType != null)
    {
        switch ($mediaType) {
            case Post::TYPE_PHOTOS:
                $qb->andWhere('postPhotos != :null')
                    ->setParameter('null', null);

The "!= :null" doesn't work, nor does COUNT(postPhotos) (obviously for aggregation reasons).

Is there a way I can specify only to bring back the posts that have 1 or more photos?

score 0 · Answer 1 · answered Mar 25 '19 at 18:50

Quick Answer: If you replace your usage of leftJoin with just join (or innerJoin) then you'll get what you want: only Posts that have at least 1 Photo.

Details

If you take a look at this helpful SO Q&A:

Different MySQL Join Methods

... you'll find some excellent venn diagrams showing the difference between a left and inner join. Then, if you look into Doctrine's Doctrine\ORM\QueryBuilder class, you'll find that they have three join methods:

join (which just calls innerJoin)
innerJoin
leftJoin

    /**
     * Creates and adds a join over an entity association to the query.
     *
     * The entities in the joined association will be fetched as part of the query
     * result if the alias used for the joined association is placed in the select
     * expressions.
     *
     * <code>
     *     $qb = $em->createQueryBuilder()
     *         ->select('u')
     *         ->from('User', 'u')
     *         ->join('u.Phonenumbers', 'p', Expr\Join::WITH, 'p.is_primary = 1');
     * </code>
     *
     * @param string      $join          The relationship to join.
     * @param string      $alias         The alias of the join.
     * @param string|null $conditionType The condition type constant. Either ON or WITH.
     * @param string|null $condition     The condition for the join.
     * @param string|null $indexBy       The index for the join.
     *
     * @return self
     */
    public function join($join, $alias, $conditionType = null, $condition = null, $indexBy = null)
    {
        return $this->innerJoin($join, $alias, $conditionType, $condition, $indexBy);
    }

    /**
     * Creates and adds a join over an entity association to the query.
     *
     * The entities in the joined association will be fetched as part of the query
     * result if the alias used for the joined association is placed in the select
     * expressions.
     *
     *     [php]
     *     $qb = $em->createQueryBuilder()
     *         ->select('u')
     *         ->from('User', 'u')
     *         ->innerJoin('u.Phonenumbers', 'p', Expr\Join::WITH, 'p.is_primary = 1');
     *
     * @param string      $join          The relationship to join.
     * @param string      $alias         The alias of the join.
     * @param string|null $conditionType The condition type constant. Either ON or WITH.
     * @param string|null $condition     The condition for the join.
     * @param string|null $indexBy       The index for the join.
     *
     * @return self
     */
    public function innerJoin($join, $alias, $conditionType = null, $condition = null, $indexBy = null)
    {
        $parentAlias = substr($join, 0, strpos($join, '.'));

        $rootAlias = $this->findRootAlias($alias, $parentAlias);

        $join = new Expr\Join(
            Expr\Join::INNER_JOIN, $join, $alias, $conditionType, $condition, $indexBy
        );

        return $this->add('join', [$rootAlias => $join], true);
    }

    /**
     * Creates and adds a left join over an entity association to the query.
     *
     * The entities in the joined association will be fetched as part of the query
     * result if the alias used for the joined association is placed in the select
     * expressions.
     *
     * <code>
     *     $qb = $em->createQueryBuilder()
     *         ->select('u')
     *         ->from('User', 'u')
     *         ->leftJoin('u.Phonenumbers', 'p', Expr\Join::WITH, 'p.is_primary = 1');
     * </code>
     *
     * @param string      $join          The relationship to join.
     * @param string      $alias         The alias of the join.
     * @param string|null $conditionType The condition type constant. Either ON or WITH.
     * @param string|null $condition     The condition for the join.
     * @param string|null $indexBy       The index for the join.
     *
     * @return self
     */
    public function leftJoin($join, $alias, $conditionType = null, $condition = null, $indexBy = null)
    {
        $parentAlias = substr($join, 0, strpos($join, '.'));

        $rootAlias = $this->findRootAlias($alias, $parentAlias);

        $join = new Expr\Join(
            Expr\Join::LEFT_JOIN, $join, $alias, $conditionType, $condition, $indexBy
        );

        return $this->add('join', [$rootAlias => $join], true);
    }

Changing your code to use innerJoin (or just join) will lead Doctrine to issue an INNER JOIN in the generated SQL which will return only records where "something" exists on both sides of the join, thus, any Post that has no Photos will not be included in the results.

(Doctrine) Select where array collection is not empty?

1 Answers1