Finding an object that has multiple tags in doctrine
I have many, many relationships between document and tag. Thus, it Document
can have several Tags
, and one Tag
can be assigned different Document
.
it Tag
AppBundle\Entity\Tag:
type: entity
table: tags
repositoryClass: AppBundle\Repository\TagRepository
manyToMany:
documents:
targetEntity: Document
mappedBy: tags
id:
id:
type: integer
id: true
generator:
strategy: AUTO
fields:
label:
type: string
length: 255
unique: true
AND Document
AppBundle\Entity\Document:
type: entity
table: documents
repositoryClass: AppBundle\Repository\DocumentRepository
manyToMany:
tags:
targetEntity: Tag
inversedBy: documents
joinTable:
name: documents_tags
id:
id:
type: integer
id: true
generator:
strategy: AUTO
fields:
title:
type: string
length: 255
Now I want to find all documents that have tags animal
and fiction
. How can I achieve this with doctrine?
Something like
$repository = $this->getDoctrine()->getRepository('AppBundle:Document');
$query = $repository->createQueryBuilder('d');
$query ->join('d.tags', 't')
->where($query->expr()->orX(
$query->expr()->eq('t.label', ':tag'),
$query->expr()->eq('t.label', ':tag2')
))
->setParameter('tag', $tag)
->setParameter('tag2', $tag2)
does not execute the task because it returns all documents that have either tag1
or tag2
. But andX
it won't work either, because there is no single tag that has both tags.
source to share
You can achieve this with additional inner joins for each tag:
Example:
$em = $this->getDoctrine()->getManager();
$repository = $this->getDoctrine()->getRepository('AppBundle:Document');
$query = $repository->createQueryBuilder('d');
$query->innerJoin('d.tags', 't1', Join::WITH, 't1.label = :tag1');
$query->innerJoin('d.tags', 't2', Join::WITH, 't2.label = :tag2');
$dql = $query->getDql();
$result = $em->createQuery($dql)
->setParameter('tag1', 'LabelForTag1')
->setParameter('tag2', 'LabelForTag2')
->getResult();
Perhaps this little image helps to understand what this request is doing. The whole circle represents all of your documents. If you are only using one connection, the request will return either green + red or blue + red part. By using an additional inner join, you only get the intersection of the unions visible individually (this is only the red part).
If you have even more tags to search for, you can simply add another connection to it.
source to share