title: Doctrine2: many-to-many joined entities, multiple andWhere or join WITH clauses?
body: <p>Two doctrine2 entities, Photo and Tag, are linked by a many-to-many relationship, and mapped accordingly.</p>
  
  <p>Each tag has a key and a value, so an example key is 'photo-type' and an example value 'people'.</p>
  
  <p>I have created a custom repository PhotoRepository.php, to facilitate easy searching for photos with either an array (or a comma-separated list*) of tag pairs, extract below:</p>
  
  <pre><code> public function getQueryByTags($tags = null, $limit =0, $start =-1, $boolean ="and")
     {
      ...
          $qb-&gt;select('p')
                  -&gt;from('MyBundle\Entity\Photo', 'p')
                  -&gt;join('p.tags','t');
                  # -&gt;join('a.tags','t')
          if ($boolean == "and") { $qb-&gt;where('1 = 1'); }
          $i = 1;
          foreach ($tags as $tag) {
              if ($boolean == "and") { 
                  $qb-&gt;andWhere('t.key = ?'.$i.' AND t.value = ?'.($i+1));
              } elseif ($boolean == "or") { 
                  $qb-&gt;orWhere('t.key = ?'.$i.' AND t.value = ?'.($i+1));
              }
              $qb-&gt;setParameter($i, $tag['key'])-&gt;setParameter(($i+1), $tag['value']);
              $i += 2;
          }
      ...
        return $qb-&gt;getQuery();
     }
  </code></pre>
  
  <p>This works fine for a single tag. However, once tags are multiple (e.g. searching for 'photo-type'=>'people', 'person'=>'Bob'), the boolean logic breaks down and no results are return.</p>
  
  <p>My suspicion is this is something to do with putting together andWhere/(orWhere) clauses from the joined Tag entity with the Doctrine2 queryBuilder(). (Since the same Tag cannot be both 'photo-type'=>'people' AND 'person'=>'Bob', although the same Photo should be).</p>
  
  <pre><code>    $photos = $em-&gt;getRepository('MyBundle:Photo')-&gt;
              findByTags(array(
                  array('key' =&gt; 'context','value' =&gt; $context),
                  array('key' =&gt; 'photo-type','value' =&gt; 'field'),
              ));
  </code></pre>
  
  <p>I tried to construct a JOIN WITH query instead, but this seems to require a very complex construction to create the expression, which I haven't been able to figure out:- </p>
  
  <pre><code>public function getQueryByTags($tags = null, $limit =0, $start =-1, $boolean ="and")
      {
      ...
          $qb-&gt;select('p')
                  -&gt;from('MyBundle\Entity\Photo', 'p');
          $i = 1;
          $expr = $qb-&gt;expr();
  
          foreach ($tags as $tag) {
              if ($boolean == "and") { 
                  $expr = $expr-&gt;andX($qb-&gt;expr()-&gt;eq('t.key', '?'.$i),$qb-&gt;expr()-&gt;eq('t.value', '?'.($i+1)));                    
              } elseif ($boolean == "or") { 
              }
              $qb-&gt;setParameter($i, $tag['key'])-&gt;setParameter(($i+1), $tag['value']);
              $i += 2;
          }
  
          $qb-&gt;join('p.tags','t',Doctrine\ORM\Query\Expr\Join::WITH,$expr);
                  # -&gt;join('a.tags','t')
        ...
        return $qb-&gt;getQuery();
     }
  </code></pre>
  
  <hr>
  
  <p>EDIT: Ultimately the result set I want is either:</p>
  
  <ul>
  <li><p>"and" search: <code>SELECT all Photos which have (Tag with key(A) AND value(B) ) AND (another Tag with key(C) AND value(D))</code></p></li>
  <li><p>"or" search: <code>SELECT all Photos which have (Tag with key(A) AND value(B) ) OR (another Tag with key(C) AND value(D))</code></p></li>
  <li><p>in which: A, B is the first unique tag 'pair' (e.g. 'photo-type'='people' or 'photo-type'='animal') and C, D is another unique tag 'pair' (e.g. 'person'='Bob', 'person'='Tiddles' or, theoretically 'animal'='Tiddles')</p></li>
  </ul>
  
  <hr>
  
  <p>Can anyone help me either figure out how to construct this complex JOIN WITH expression? </p>
  
  <p>Or, if it seems like I'm barking up the wrong tree, can anyone suggest an alternative more elegant way to do things?</p>
  
  <p>*NB: If $tags is received as comma-separated string (e.g. $tags="photo-type=people,person=Bob") it is first converted into an array.</p>
  
  <hr>
  
  <p>EDIT#2: the Tag entity, on request from @Wilt:</p>
  
  <p>Tag.yml</p>
  
  <pre><code>MyBundle\Entity\Tag:
      type: entity
      table: tag
      fields:
          id:
              id: true
              type: integer
              unsigned: true
              nullable: false
              generator:
                  strategy: IDENTITY
          key:
              type: string
              length: 20
              fixed: false
              nullable: true
          value:
              type: string
              length: 50
              fixed: false
              nullable: true
      manyToMany:
          photos:
              targetEntity: Tag
              mappedBy: tags
      lifecycleCallbacks: {  }
  </code></pre>
  
  <p>Photo.yml (extract only)</p>
  
  <pre><code>MyBundle\Entity\Photo:
      type: entity
      repositoryClass: MyBundle\Entity\PhotoRepository
      table: photo
      fields:
          sha1:
  ....
      manyToMany:
          tags:
              targetEntity: Tag
              inversedBy: photos
              joinTable:
                  name: x_photo_tag
                  joinColumns:
                      photo_sha1:
                          referencedColumnName: sha1
                  inverseJoinColumns:
                      tag_id:
                          referencedColumnName: id
  </code></pre>