Symfony Doctrine没有重复的价值

I take hours trying to make the query with Doctrine to read locations data (Location entity) based on the distance but in turn the value of location (Location entity) only repeat once for the respective user (Chauffeur entity).

My Location entity:

• id
• chauffeur (ManyToOne to Chauffeur entity)
• latitude
• longitude
• accuracy
• altitude
• ....

My Chauffeur entity:

• id
• name
• email
• ....

My current code:

   $Locations = $entityManager
        ->getRepository("MYBUNDLE:Location")
        ->createQueryBuilder('s')
        ->leftJoin("s.chauffeur", "chauffeur")
        ->select('s')
        ->addSelect(
            '( 3959 * acos(cos(radians(' . $location_lat . '))' .
                '* cos( radians( s.latitude ) )' .
                '* cos( radians( s.longitude )' .
                '- radians(' . $location_lng . ') )' .
                '+ sin( radians(' . $location_lat . ') )' .
                '* sin( radians( s.latitude ) ) ) ) as distance'
        )
        ->having('distance < :distance')
        ->setParameter('distance', 10)
        ->orderBy('distance', 'ASC')

        // up to this line it works fine

        //->select('s.chauffeur,count(s.1) as cnt')   //
        //->groupBy('s.chauffeur')                    // Filter to see only one Location per Chauffeur
        //->having('s.count(1) > 1');                 //

My select result:

• First result:

  • id: 1
  • chauffeur: 10 (id)
  • latitude: -34.894336
  • longitude: -56.152603
  • accuracy: 20.0
  • altitude: 20

• Second result:

  • id: 2
  • chauffeur: 15 (id)
  • latitude: -34.917782
  • longitude: -56.164569
  • accuracy: 20.0
  • altitude: 20

• Third result:

  • id: 3
  • chauffeur: 10 (id)
  • latitude: -34.902652
  • longitude: -56.147251
  • accuracy: 20.0
  • altitude: 20

• ...

What should happen is that the location of ID 3 is omitted because there is another Location (ID 1) for the same user (Chauffeur entity)

I appreciate your help, Greetings,

This code will return all Chauffer with more than 1 locations:

 ->select('s.chauffeur,count(s.1) as cnt')   //
 ->groupBy('s.chauffeur')                    // Filter to see only one Location per Chauffeur
 ->having('s.count(1) > 1');                 //

You want return the one where Chauffer is closser. Lets assume you have distance in the Location table, or you can calculate using a function.

 SELECT id, Chauffer, f_distance(location_lat, $location_lng) as distance
 FROM Location

First case, distance is already in Location.

This will return Location where the distance is the minimal for each Chauffer. Be aware this can return multiple rows if have multiple location with same distance.

 SELECT L.chauffeur, L.latitude, L.longitude L,accuracy, L.altitude, L.distance
 FROM Location L 
 WHERE L.distance = (SELECT MIN(distance)
                     FROM Location M
                     WHERE M.chauffeur = L.chauffeur)

You can add another where to select the smallest ID

AND NOT EXISTS ( SELECT 1
                 FROM Location M 
                 WHERE M.id > L.id
                   AND M.distance = L.distance