Doctrine2 How to compare two result sets

Question

I have two entities: Category and Icon they have a many to many relationship so i end up with three tables: category icon and icon_category

My goal is to find Icons that are in multiple categories.

For example I have the following

categories: a b c and icons 1 2 3

Here are the categories for the icons:

1 - a b

2 - a

3 - c

I would like to search for an icon that is in category a and b and get 1 as the result.

My first approach was to load in each category (a and b) into separate results and then compare using array_intersect():

  $cats = array();

  foreach($terms as $term){
      $cat = $em->getRepository('SixStringPearBundle:Category')->findOneBy(array("name" => $term));
      if($cat){
         $cats[$term] = $cat->getIcons();
      }
  }

This returned $cats[a] = array(icon(1), icon(2) and $cats[b] = array(icon(1))

I then tried the following:

    $res = array_shift($cats);
    foreach($cats as $cat){
        $res = array_intersect($res, $cat);
    }

but got the following error: Argument #1 is not an array

I checked the type of $cat[a] and $cat[b] and they are a Doctrine Persistence Collection

I also tried calling $res = $res->toArray() and $cat = $cat->toArray() before calling array_intersect This resolved the error but did not return the expected results: Icon(1)

Does anyone have any thoughts or maybe even a better approach to all of this?

Answer 1

I ended up using the doctrine query builder. It was agonizing but I finally figure it out. Here is the end result:

$qb->select('i')
   ->from('SixStringPearBundle:Icon', 'i')
   ->leftJoin('i.categories', 'c')
   ->where('c.name IN (?1)')
   ->groupBy('i.id')
   ->having('count(i.id) = ?2')
   ->setParameters(array(1 => $terms, 2 => count($terms)));