Reputation: 3374
I have a many to many relation between Document and Tag. So a Document
can have several Tags
's, and one Tag
can be assigned to different Document
's.
This is 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 search for all Documents that has the tags animal
and fiction
. How can I achieve that 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)
wont do the job, because it returns all Documents that have either tag1
or tag2
. But andX
won't work too, because there is no single tag that has both labels.
Upvotes: 3
Views: 1125
Reputation: 607
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();
Maybe this little image helps understanding what this query does. The whole circle represent all your documents. If you are only using one single join, the query will return either the green+red or the blue+red part. Using an additional inner join, you will only get the intersection of the joins seen individually (which is only the red part).
If you have even more tags to search for, you can simply add another join for that.
Upvotes: 8