CODEWITHSUNDEEP
phpsymfonydoctrineentitiesdql
gomman
gomman

Reputation: 133

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.

Upvotes: 1

Views: 1100

Answers (1)

Nicolai Fr&#246;hlich
Nicolai Fr&#246;hlich

Reputation: 52493

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();
}

Upvotes: 1

Related Questions