Doctrine - how to check if a collection contains an entity

Question

I have two entities User and Article with many-to-many relation as Article can have many authors.

class User
{
    /** @var string */
    public $name;

    /** @var Collection<Article> */
    public $articles;
}

class Article
{
    /** @var string */
    public $title;

    /** @var Collection<User> */
    public $authors;
}

How I can find all Articles with specified (co)author using DQL?

Boethius

Answer 1

Use MEMBER OF expression.

Your DQL query could like like

SELECT art FROM Article art WHERE :user MEMBER OF art.authors

or using query builder

$queryBuilder = $repository->createQueryBuilder("art");
$queryBuilder->where(":user MEMBER OF art.authors");

---

Alternatively you can join and filter collection

SELECT art FROM Article art JOIN art.authors aut WHERE aut = :user

or

$queryBuilder = $repository->createQueryBuilder("art");
$queryBuilder->join("art.authors", "aut");
$queryBuilder->where("aut = :user");

Boethius

Answer 2

Use Query Builder

Summary

$qb->expr()->isMemberOf(':user', 'a.authors')

Solution

src/Repository/ArticleRepository.php

/**
 * @param User $author
 * @return Article[] List of articles filtered by $author
 */
public function findByAuthor(User $author): array
{
    $qb = $this->createQueryBuilder('a');

    $qb->setParameter('user', $author);

    $qb->where($qb->expr()->isMemberOf(':user', 'a.authors'));

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

Example use

src/Controller/ArticleController.php

/**
 * @Route("/article/{id<\d+>}", name="show-articles-by-author")
 * @param ArticleRepository $articleRepository
 * @param User $author
 */
public function showArticlesFromAuthor(ArticleRepository $articleRepository, User $author)
{
    $articles = $articleRepository->findByAuthor($author);

    return $this->render('articles.html.twig', [
        'articles' => $articles, 
        'author'   => $author->getName()
    ]);
}

templates/articles.html.twig

<h1>Articles from {{ author }}</h1>

<li>
    {% for article in articles %}
        <ul>
            {{ article.title }}
        </ul>
    {% endfor %}
</li>
    

Brookes