Symfony Doctrine take the most recent object result : Having clause

Question

I'm trying to get the recent document from my database, I have created this queryBuilder to get the Document obeject but it seems not working good when I add the clause Having.
This my query:

    $qb = $this->createQueryBuilder('sm');

    $qb
        ->andWhere('sm.supplier = :supplier')
        ->setParameter('supplier', $supplierId)
        ->andHaving('MAX(sm.createdAt)')
        ->groupBy('sm.id');
    return $qb->getQuery()->getOneOrNullResult();

And this is the exception I'm getting:

{"code":500,"message":"[Syntax Error] line 0, col -1: Error: Expected =, <, <=, <>, >, >=, !=, got end of string."}

This my DQL:
"SELECT sm FROM SupplierBundle\\Entity\\SupplierMedia sm WHERE sm.supplier = :supplier AND sm.type = :type GROUP BY sm.id HAVING MAX(sm.createdAt)"

score 1 · Accepted Answer · answered Jun 15 '18 at 12:25

1

HAVING expression defines conditions that will be applied on the result set after WHERE and GROUP BY.

If you just need to get last record from a table apply limit and order by.

answered Jun 15 '18 at 12:25

Vadim Ashikhman

9,851
1
35
39

->setMaxResults(1); youmean by limit ? – famas23 Jun 15 '18 at 12:39
It gives me the oldest element nit the recent – famas23 Jun 15 '18 at 12:43
Well, just change `order by` direction, like `->orderBy('a.createdAt DESC')` – Vadim Ashikhman Jun 15 '18 at 12:44
This is the right syntaxe bro `orderBy('sm.createdAt', 'DESC')` any way thank you forr help :)I really appreciate good idea (y) – famas23 Jun 15 '18 at 12:51

score 1 · Answer 2 · answered Jun 15 '18 at 12:26

1

To get the recent object for each supplier you could go with a self join approach

SELECT a 
FROM  SupplierBundle\Entity\SupplierMedia a
    LEFT JOIN SupplierBundle\Entity\SupplierMedia b 
    WITH a.supplier = b.supplier 
    AND a.createdAt < b.createdAt
WHERE b.createdAt IS NULL
AND a.supplier = :supplier 
AND a.type = :type

Doctrine Query Language get Max/Latest Row Per Group

answered Jun 15 '18 at 12:26

M Khalid Junaid

63,861
10
90
118

@ahmedbhs Its DQL already, if you want to the query builder version you follow the reference link [**Doctrine Query Language get Max/Latest Row Per Group**](https://stackoverflow.com/questions/46106582/doctrine-query-language-get-max-latest-row-per-group/46233080#46233080) – M Khalid Junaid Jun 15 '18 at 13:14

Symfony Doctrine take the most recent object result : Having clause

2 Answers2