When building queries in Doctrine, we sometimes need to combine AND and OR conditions. In this case we want isPublished and visibility to be required, with article, category or tag matching acting as an alternative.

So we are interested in a query in the form:

WHERE a.isPublished = 1
  AND a.publishedAt < :now 
	AND a.visibility IN (...) 
	AND (
		a IN (...) 
		OR c IN (...) 
		OR t IN (...) 
	)

QueryBuilder

First, we create a standard QueryBuilder and attach relationships with categories and tags:

	$qb = $this
    ->createQueryBuilder('a')
    ->leftJoin('a.categories', 'c')
		->leftJoin('a.tags', 't')
		->orderBy('a.sortAt', 'DESC')
		->setMaxResults($limit)
		->distinct();

distinct() is important here because an article may belong to several categories and have several tags. Therefore, after performing a JOIN, the same article may appear in the results more than once.

Then we add conditions that must always be met:

	$qb
		  ->andWhere('a.isPublished = 1')
	  	->andWhere('a.publishedAt < :now')
  		->setParameter('now', new \DateTimeImmutable())
	;

If the user is not logged in, we also add the appropriate visibility:

  if (null === $user) {
  		$qb
	  	  	->andWhere('a.visibility IN (:visibility)')
		    	->setParameter('visibility', Visibility::getForUnlogged())
  		;
	}

OR conditions

Now we want to add conditions that do not have to be met simultaneously.

An article may match directly to the provided list of articles, belong to one of the categories or have one of the specified tags.

We can use orX() for this:

$or = $qb->expr()->orX();

Then we add individual conditions:

  if (!empty($articles)) {
		  $or->add('a IN (:articles)');
	  	$qb->setParameter('articles', $articles);
	}
	
	if (!empty($categories)) {
  		$or->add('c IN (:categories)');
		  $qb->setParameter('categories', $categories);
	} 
	
	if (!empty($tags)) {
  		$or->add('t IN (:tags)');
	  	$qb->setParameter('tags', $tags); 
	}

Depending on the parameters passed, $or may contain, for example:

a IN (:articles)
	OR c IN (:categories)
	OR t IN (:tags)

Finally, we add the entire group to the query:

  if ($or->count() > 0) {
  		$qb->andWhere($or);
	}

The important thing here is that we add the entire orX() using andWhere(). This will allow Doctrine to treat all OR conditions as one group:

  AND (
		a IN (...)
		OR c IN (...) 
		OR t IN (...) 
	)

This gives us exactly the behavior we need – the article must be published and available, but when it comes to matching, one of the conditions only needs to be met.

All code

Finally, QueryBuilder looks like this:

       $qb = $this
            ->createQueryBuilder('a')
            ->leftJoin('a.categories', 'c')
            ->leftJoin('a.tags', 't')
            ->orderBy('a.sortAt', 'DESC')
            ->setMaxResults($limit)
            ->distinct()
        ;

       $qb
            ->andWhere('a.isPublished = 1')
            ->andWhere('a.publishedAt < :now')
            ->setParameter('now', new \DateTimeImmutable())
        ;

        if (null === $user) {
            $qb
                ->andWhere('a.visibility IN (:visibility)')
                ->setParameter('visibility', Visibility::getForUnlogged())
            ;
        }

        $or = $qb->expr()->orX();

        if (!empty($articles)) {
            $or->add('a IN (:articles)');
            $qb->setParameter('articles', $articles);
        }

        if (!empty($categories)) {
            $or->add('c IN (:categories)');
            $qb->setParameter('categories', $categories);
        }

        if (!empty($tags)) {
            $or->add('t IN (:tags)');
            $qb->setParameter('tags', $tags);
        }

        if ($or->count() > 0) {
            $qb->andWhere($or);
        }

        return $qb->getQuery()->getResult();

As a result, we get a query logically corresponding to:

	WHERE 
		a.isPublished = 1 
		AND a.publishedAt < :now 
		AND a.visibility IN (...) 
		AND (
			a IN (...) 
			OR c IN (...) 
			OR t IN (...)
		)

orX() is therefore a simple way to create a group of OR conditions, which we can then connect to the rest of the query using AND.