Q:

How to query doctrine for dependent entities in Symfony2 controller

I don't find an example here in SO, so i post my question:
I have a group entity, a shop entity, and a transaction entity.
A group has many shops, and a shop can belong to many groups. In Group.php:
/**
    * @ORM\ManyToMany(targetEntity="Shop", inversedBy="groups")
    * @ORM\JoinTable(name="group_shop",
*      joinColumns={@ORM\JoinColumn(name="group_id", referencedColumnName="id")},
*      inverseJoinColumns={@ORM\JoinColumn(name="shop_id", referencedColumnName="id")}
*      )
**/
private $shops;

And in Shop.php
/**
* @ORM\ManyToMany(targetEntity="Group", mappedBy="shops")
*/
private $groups;

Then, a shop makes transactions. In Transaction.php:
/**
* @ORM\ManyToOne(targetEntity="Shop", inversedBy="transactions")
* @ORM\JoinColumn(name="shop_id", referencedColumnName="id")
* */
private $shop;

And in Shop.php:
/**
* @ORM\OneToMany(targetEntity="Transaction", mappedBy="shop")
**/
private $transactions;

What I want to query is all transactions from a group. This must be very simple, buy i'm blinded.
What I have:
$query4 = $em->createQuery("SELECT t FROM MGFAppBundle:Transaction t 
WHERE t.date > :from AND t.date < :to AND t.shop IN (/* HERE I'M STUCK */)")-    >setParameters(array(
         'from' => $from
         'to' => $to
      ));

I don't know if this is the correct approach or... well, dql is kinda hard to get for me.
How do I write this dql query?
Thanks in advance.

A:

In your repository do something like this:
public function findTransactionsByGroup(GroupInterface $group)
{
    return $this->createQueryBuilder('g')
        ->select('s.transactions')
        ->leftJoin('g.shops','s')
        ->where('s.group = :groupid')
        ->setParameter('groupid', $group->getId())
        ->getQuery()
        ->getResult();
}

