Doctorine many to many relational query with distinct result

1
votes

I am using symfony2 for my app. I have two entities, Content and Albums with ManyToMany relationship. For example, one content may have many albums and vice versa.

$em->getRepository('MyAppBundle:Content')
        ->createQueryBuilder('c')
        ->select('c.id, c.title, c.sequence, c.sequence_count, c.category_sequence, c.unique_id, c.priority, c.status')
        ->addSelect('o.slug as owner')
        ->addSelect('cat.slug as category')
        ->addSelect('a.name as album')
        ->innerJoin('c.content_owner', 'o')
        ->innerJoin('c.category', 'cat')
        ->leftJoin('c.albums', 'a')
        ->getQuery()
        ->getArrayResult();

With this query I want to list all contents whether it has album or not, it works fine but if one content has two album the content print twice. How can I avoid the duplicate?

Thanks

2
mysqlsymfonydoctrine-orm
use distinct or group by as per yorr requirement to eliminate duplicity.. - Zafar Malik
have you tried dictinct or group by? ->select('DISTINCT c.id, c.title, ...) - Benno Eggnauer
thanks for the suggetions, but if i add groupBy('c.title') then only one album name is showing even if the content have multiple album name, what to do? - Ahmad Sajid

2 Answers

0
votes

If you have your entities relationships configured properly (http://doctrine-orm.readthedocs.org/en/latest/reference/association-mapping.html) you can simply list the contents and loop through the "albums" array to display the corresponding albums. This means you do not select the albums in your query builder.

By default this will use lazy loading, which means the albums will be fetched separately for each content, to avoid this you keep the line ->leftJoin('c.albums', 'a')

$em->getRepository('MyAppBundle:Content')
   ->createQueryBuilder('c')
   ->leftJoin('c.albums', 'a')
   ->getQuery()
   ->getResult();

You will get hydrated objects which the array albums populated.

Unfortunately I don't know which configuration format you are using nor the templating engine so I can't show you an example of the rendering part.

0
votes

I have solved that with the group_concat function. I had to use the beberlei/DoctrineExtensions extension as Doctorine2 not yet supporting the group_concat function:

https://github.com/beberlei/DoctrineExtensions

here is my final code:

 $contents = $em->getRepository('MyAppBundle:Content')
        ->createQueryBuilder('c')
        ->select('c.id, c.title, c.sequence, c.sequence_count, c.category_sequence, c.unique_id, c.priority, c.status')
        ->addSelect('o.slug as owner')
        ->addSelect('cat.slug as category')
        ->addSelect("group_concat(m.name SEPARATOR ',') AS media")
        ->addSelect("group_concat(a.name SEPARATOR ',') AS album")
        ->innerJoin('c.content_owner', 'o')
        ->innerJoin('c.category', 'cat')
        ->leftJoin('c.media', 'm')
        ->leftJoin('c.albums', 'a')
        ->groupBy('c.id')
        ->getQuery()
        ->getArrayResult();