Symfony Query Builder how to get last grouped records

Question

I have an entity named "Location" with the following fields:

id (auto increment)
vehicle (foreign key to "vehicle" table)
lat
lng
speed

I need to get the last locations (with last id) grouped by vehicle using doctrine Query Builder.

Here is my function:

// Find locations
public function findLocations($idFleet, $select, $from, $to)
{
    $qb = $this->createQueryBuilder('l')
    ->innerJoin('l.vehicle', 'v')
    ->where('v.fleet = :fleet')
    ->setParameter('fleet', $idFleet)
    ->andWhere('v.gps = :gps')
    ->setParameter('gps', true);

    // Last locations
    if ( $select == "last"){
        $qb->groupBy('l.vehicle')
            ->orderBy('l.id', 'ASC');            
    }
    // else Interval locations
    else if ( $select == "interval"){
        if( $from != ""){
            $from = (new \DateTime())->setTimestamp($from);
            $qb->andWhere('l.time >= :from')
            ->setParameter('from', $from);
        }
        if( $to != ""){
            $to = (new \DateTime())->setTimestamp($to);
            $qb->andWhere('l.time <= :to')
            ->setParameter('to', $to);
        }
    }
    $locations = $qb->getQuery()->getResult();
    return $locations;   
}

thanks for helping.

score 1 · Answer 1 · answered Jul 11 '18 at 05:28

To pick the latest record from location entity for each vehicle you could do a self left join on location entity with additional join criteria using WITH clause and in where clause check for nulls, the rows with highest id per location will have a null against self joined rows.

// Last locations
if ( $select == "last"){
    $qb
    ->leftJoin( 'YourBundle\Entity\Location', 'l1', 'WITH', 'l.vehicle = l1.vehicle AND l.id < l1.id' )
    ->andWhere('l1.vehicle IS NULL' )
    ->orderBy('l.id', 'ASC');            

}

See similar solutions

Dimitris · Answer 2 · 2018-07-10T17:09:30.727

I had a similar problem recently. This can be done using a subquery to get the location info by max location id for the vehicle, and then left join it to the outer vehicle select.

Unfortunately this cannot be done in DQL since it cannot join an entity with a subquery.

You will have to do this in native SQL

Something like

SELECT * from vehicle LEFT JOIN location ON location.vehicle = vehicle.id WHERE location.id in (SELECT MAX(Id) FROM location GROUP BY vehicle)

You might think at first that you can do this in a DQL subquery, but this query will work only if there is at least one location for each vehicle.

If you want to still get the vehicle data even if there is no location entry for that vehicle, you will have to work on the subquery to get * for it having max id group by vehicle, and then left join it to the main query. This is the part not supported in DQL

Symfony Query Builder how to get last grouped records

2 Answers2