Asara
Asara

Reputation: 3374

Search for Entity that has multiple tags in doctrine

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

Answers (1)

csc
csc

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).

enter image description here

If you have even more tags to search for, you can simply add another join for that.

Upvotes: 8

Related Questions