0

I have the following query:

 $query = $em->createQueryBuilder()->select('p')
                    ->from("ShopMainBundle:ShopPicture", 'p')
                    ->innerJoin('p.shop', 'shop')
                    ;

I wanted to get the pictures from each unique shop. How do I do this? In other words I want to specify that each p should belong to X.

adit
  • 32,574
  • 72
  • 229
  • 373

1 Answers1

0

Not sure if I understand correctly, but the following should work:

$query = $em->createQueryBuilder()->select('shop', 'p')
                ->from("ShopMainBundle:Shop", 'shop')
                ->innerJoin('s.pictures', 'p')
                ->getQuery()
                ;
foreach ($query->getResult() as $shop) {
    foreach ($shop->getPictures() as $picture) {
        ...
    }
}

Edit

If you want to get one picture for every shop, you can group by the shop:

$query = $em->createQueryBuilder()->select('p')
                ->from("ShopMainBundle:ShopPicture", 'p')
                ->groupBy('p.shop')
                ;

You however cannot get one random picture since Doctrine does not support RAND(). See How to select randomly with doctrine for more info

Community
  • 1
  • 1
sroes
  • 14,663
  • 1
  • 53
  • 72
  • well..I'd want to do it inside the query.. essentially I wanted to get pictures from unique shops – adit Feb 12 '14 at 08:14
  • Could you maybe provide us with the raw SQL you're trying to create? I'm not sure what you're trying to achieve. – sroes Feb 12 '14 at 08:21
  • I don't even know how to do it using RAW SQL.. Basically I wanted to get one random picture from each unique shops that is in the database.. if that makes any sense. The relationship is that a shop can have many pictures – adit Feb 12 '14 at 08:41