Doctrine - QueryBuilder - get youngest entity in result

Question

I have this table structure:

CREATE TABLE `inventory_item` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `articleID` int(11) NOT NULL,
  `quantity` int(11) NOT NULL,
  `date` datetime DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

And I have this query:

$rows = $this->getModelManager()->createQueryBuilder()
            ->select('ii')
            ->from(InventoryItem::class, 'ii')
            ->where('ii.articleId IN (:articleIds)')
            ->andWhere('ii.quantity > 0')
            ->orderBy('ii.date', 'ASC')
            ->setParameter('articleIds',  $articleIds )
            ->getQuery()
            ->getResult();

In the Database I can have entities which look like this:

ID | ArticleID | Quantity | Date
1  | 100       |     10    | 2018-08-31
2  | 200       |     20    | 2018-07-31
3  | 100       |     40    | 2018-05-31

Now, when $articleIds in the query are 100, 200 I want to have this output:

ID | ArticleID | Quantity | Date
2  | 200       |     20    | 2018-07-31
3  | 100       |     40    | 2018-05-31

So, when ArticleID is equal the query should only return the entity with the youngest date, but also the entity with ArticleId = 200.

Is there a possibility in the doctrine query builder to achieve this? I tried it with groupBy, but this does not work as the orderBy has no effect on the result when using groupBy.

Thanks!

score 0 · Answer 1 · answered May 28 '18 at 16:28

You can sort by DESC and tell to Doctrine to return max one result, as example:

$query = $this->getModelManager()->createQueryBuilder()
            ->select('ii')
            ->from(InventoryItem::class, 'ii')
            ->where('ii.articleId IN (:articleIds)')
            ->andWhere('ii.quantity > 0')
            ->orderBy('ii.date', 'DESC')
            ->setParameter('articleIds',  $articleIds )
            ->getQuery();

$result = $query
->setMaxResults(1)            
->getResult();

Hope this help

score 0 · Answer 2 · answered Jun 05 '18 at 10:25

To get the the oldest row based on date property per article you could use a self join with your entity, In DQL it can be expressed as

SELECT a 
FROM YourBundle\Entity\InventoryItem a
    LEFT JOIN YourBundle\Entity\InventoryItem b 
    WITH a.articleId  = b.articleId  
    AND a.date > b.date
WHERE b.articleId IS NULL
ORDER BY a.date DESC

Using query builder you might rewrite it as

$DM   = $this->get( 'Doctrine' )->getManager();
$repo = $DM->getRepository( 'YourBundle\Entity\InventoryItem' );
$results = $repo->createQueryBuilder( 'a' )
                ->select( 'a' )
                ->leftJoin( 'YourBundle\Entity\InventoryItem', 'b', 'WITH', 'a.articleId = b.articleId AND a.date > b.date' )
                ->where( 'b.articleId IS NULL' )
                ->orderBy( 'a.date','DESC' )
                ->getQuery()
                ->getResult();

Doctrine Query Language get Max/Latest Row Per Group

Doctrine - QueryBuilder - get youngest entity in result

2 Answers2