Doctrine Query Language Latest Row Per Group
this should get you the result you want
public function selectLastOrdersPerCustomer(){
return $this->createQueryBuilder('o')
->leftJoin(Order::class, 'o2', 'WITH', 'o2.customer = o.customer AND o.timestamp < o2.timestamp')
->where( 'o2.timestamp IS NULL' )
->innerJoin('o.customer', 'c')
->addSelect('c')
->groupBy('c.id')
->orderBy('o.timestamp', 'DESC')
->getQuery()
->getResult();
}
Doctrine Query Language get Max/Latest Row Per Group
The query you are trying to do with doctrine is related to greatest-n-per-group. To use a sub query and then join with main query get things complicated to handle with doctrine. So below is the rewritten SQL version to get the same results without use of any aggregate functions:
SELECT
a.*
FROM
score a
LEFT JOIN score b
ON a.name = b.name
AND a.score < b.score
WHERE b.score IS NULL
ORDER BY a.score DESC
DEMO
To convert above query equivalent to doctrine or DQL is easy, below is the DQL version of above SQL:
SELECT a
FROM AppBundle\Entity\Score a
LEFT JOIN AppBundle\Entity\Score b
WITH a.name = b.name
AND a.score < b.score
WHERE b.score IS NULL
ORDER BY a.score DESC
Or with query builder you can write something like i have tested below with symfony 2.8 using the DEMO Schema
$DM = $this->get( 'Doctrine' )->getManager();
$repo = $DM->getRepository( 'AppBundle\Entity\Score' );
$results = $repo->createQueryBuilder( 'a' )
->select( 'a' )
->leftJoin(
'AppBundle\Entity\Score',
'b',
'WITH',
'a.name = b.name AND a.score < b.score'
)
->where( 'b.score IS NULL' )
->orderBy( 'a.score','DESC' )
->getQuery()
->getResult();
Another idea would be create a view using your query in database and in symfony create an entity put the view name in table annotation and just start calling your entity it will give the results returned by your query but this approach is not recommended just a temporary fix.
doctrine dbal get latest chat message per group
As your question is unclear so i am assuming you need to get the recent chat/message per group, the equivalent SQL for this will be
SELECT c.id, c.message, a.name as chat_from, c.chat_to as count
FROM account a
JOIN chat c ON(c.chat_from = a.id )
LEFT JOIN chat cc ON(c.chat_from = cc.chat_from AND c.date_sent < cc.date_sent)
WHERE cc.date_sent IS NULL AND c.chat_to = @id
ORDER BY c.date_sent DESC
So using doctrine dbal you can write above query as
$this->qb->select( 'c.id', 'c.message', 'a.name as chat_from', 'c.chat_to as count' )
->from( 'account', 'a' )
->join( 'a', 'chat', 'c', 'c.chat_from = a.id' )
->leftJoin( 'c', 'chat', 'cc', 'c.chat_from = cc.chat_from AND c.date_sent < cc.date_sent' )
->where( 'cc.date_sent IS NULL' )
->andWhere( 'c.chat_to =' . $id )
->orderBy( 'c.date_sent', 'DESC' )
->execute();
Again without viewing the sample data and DDL its not a complete solution.
Reference
DQL Select every rows having one column's MAX value
After some hours of headache and googling and stackOverflow readings...
I finally found out how to make it.
Here is my final DQL queryBuilder code:
$qb = $this->createQueryBuilder('a');
$qb2= $this->createQueryBuilder('mss')
->select('MAX(mss.periodeComptable) maxPeriode')
->where('mss.affaire = a')
;
$qb ->innerJoin('GAAffairesBundle:MontantMarche', 'm', 'WITH', $qb->expr()->eq( 'm.periodeComptable', '('.$qb2->getDQL().')' ))
->where('a = :affaire')
->setParameter('affaire', $affaire)
;
return $qb->getQuery()->getResult();
Symfony Doctrine take the most recent object result : Having clause
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.
Related Topics
Echo a Multidimensional Array in PHP
How to Get Xdebug Var_Dump to Show Full Object/Array
Yosemite/El Capitan PHP-Gd + Mcrypt Installation
PHP - Convert Multidimensional Array to 2D Array with Dot Notation Keys
Jquery Ajax Request with JSON Response, How To
How to Use Pdo's Prepared Statement for Order by and Limit Clauses
Composer Installing: the JSON Extension Is Missing
How to Protect from Downloading a Video from a Site
Deleting All Files from a Folder Using PHP
Laravel Eloquent Sum of Relation's Column
How to Check If Time Is Between Two Times in PHP
PHP JSON Encode - Malformed Utf-8 Characters, Possibly Incorrectly Encoded