0

I have a fairly complex query as follows:

return $this->createQueryBuilder('s')
                ->select('s') 
                ->addSelect('COUNT(p.id) as HIDDEN c_id')
                ->leftJoin('s.owner', 'o')
                ->leftJoin('s.userPictures', 'p')
                ->leftJoin('o.transactions', 't')
                ->leftJoin('t.packType', 'pt')
                ->where('pt.id =:packId')
                ->setParameter('packId', $packId)
                ->andWhere('s.expirydate >=:expiryDate')
                ->setParameter('expiryDate', new \DateTime('now'))
                ->andWhere('c_id <:numberOfPictures')
                ->setParameter('numberOfPictures', $numberOfPictures)
                ->orderBy("c_id", 'DESC')
                ->groupBy('p.id')
                ->getQuery()
                ;

the problem is that the query is leftJoined with all of it's transactions. I wanted such that it is left joined with the most recent transaction only. How can I do this? Is there an alternative way other than having to find the transaction id of the most recent transaction and put it into the where clause?

The Transaction entity has a created column and the entity looks like this:

class Transaction
{


    /**
     * @var datetime $created
     * @Gedmo\Timestampable(on="create")
     * @ORM\Column(type="datetime")
     */
    protected $created;
}
adit
  • 32,574
  • 72
  • 229
  • 373
  • Take a look [here](http://dev.mysql.com/doc/refman/5.7/en/example-maximum-column-group-row.html), the last solution uses a `LEFT JOIN`. – MatTheCat Dec 08 '14 at 08:47
  • @MatTheCat don't understand how a left join is supposed to solve this issue – adit Dec 08 '14 at 09:51
  • "The LEFT JOIN works on the basis that when [t1.created] is at its maximum value, there is no [t2.created] with a greater value and the s2 rows values will be NULL." – MatTheCat Dec 08 '14 at 13:17
  • Possible duplicate of [Doctrine Query Language get Max/Latest Row Per Group](https://stackoverflow.com/questions/46106582/doctrine-query-language-get-max-latest-row-per-group) – M Khalid Junaid Dec 05 '17 at 05:07

0 Answers0