MYSQL JOIN et GROUP / DISTINCT

Question

J'ai 3 tables que je joins pour obtenir les utilisateurs d'une zone spécifique. Un exemple réduit des tables :

USER Table (stores all user information) 
ID | Name
----------
 1   John
 2   Joe
 3   Mike 

GEO (has all geo location info; including latitude and longitude; which im excluding for the example )
ID | CITY 
-------------
 1 | ORLANDO
 2 | MIAMI
 3 | DAYTONA

LOCATIONS (stores each users location; each user has multiple locations)
ID | AREA (id = user.id, geo = geo.id)
--------
 1 | 1
 1 | 2
 1 | 3
 2 | 1
 3 | 1
 3 | 3

J'ai créé une fonction en php pour obtenir les résultats pour un LAT / LONG donné dans un certain rayon comme suit (en excluant la fonction entière car elle n'est pas pertinente) :

select USER.ID as USERID, (6371 * acos(cos(radians( {$lat})) * cos(radians(g.latitude)) * cos(radians(g.longitude) - radians({$long})) + sin(radians({$lat})) * sin(radians(g.latitude)))) AS distance
            from 
            GEO G 
            join LOCATIONS LOC on LOC.AREA = G.ID
            join USER U on LOC.ID = USERID
            HAVING distance <= {$radius}

Maintenant, la question. Cela fonctionne et permet d'obtenir toutes les informations, mais le même utilisateur apparaît plusieurs fois parce qu'il se trouve plusieurs fois dans la table LOCATIONS (par exemple, 100 résultats, avec 15 utilisateurs différents).

J'ai donc pensé à GROUPER PAR USER.id, mais cela ne correspond qu'au premier emplacement de cet utilisateur, ce qui ne donne que 2 résultats.

J'ai essayé DISTINCT ; mais les lignes ne sont pas distinctes car le user.id ou le location.id sont une combinaison différente pour chaque ligne.

J'ai également essayé de travailler à l'envers avec des sous-requêtes

SELECT * from USER where id = (
select id from GEO where area = (
select id, (long trig here) as distance) from GEO)

Mais cela ne fonctionne pas car je dois sélectionner la déclaration trigonométrique en tant que distance, je ne peux donc pas simplement sélectionner l'identifiant dans la table GEO.

Je ne sais plus où donner de la tête pour obtenir des utilisateurs uniques, mais je dois quand même faire une recherche dans tous les emplacements de l'utilisateur. Je sais que je pourrais boucler les résultats en php et les reconstruire, mais cette requête renvoie facilement des milliers de résultats puisque chaque emplacement d'utilisateur est affiché dans les résultats et je préférerais ne pas le faire pour des raisons de rapidité.

Toute aide dans la bonne direction serait très appréciée.

ADDITION

Pour approfondir un peu la question des résultats, si vous exécutez cette requête sur ORLANDO avec un rayon qui s'étend jusqu'à DAYTONA, si un utilisateur se trouve à DAYTONA, vous obtiendrez

USER | CITY
-----------
 1  | ORLAND
 1  | DAYTONA
 2  | ORLANDO
 3  | ORLANDO
 3  | DAYTONA

ce qui a pour effet de dupliquer les utilisateurs 1 et 3

mais lorsque l'on regroupe par user.id, on obtient seulement

 USER | CITY
-----------
 2  | ORLANDO

ce qui exclut les utilisateurs 1 et 3, car lorsque le groupe est groupé, il n'indique que leur région comme DAYTONA.

Answer 1

Si vous utilisez WHERE au lieu de HAVING vous serait être capable d'utiliser GROUP BY / DISTINCT et attraper est comme ça :

SELECT u.id AS USERID
    FROM `GEO` g
    JOIN `LOCATIONS` l ON l.`AREA` = g.`ID`
    JOIN `USER` u ON l.`ID` = u.`ID`
    WHERE (6371 * ACOS(COS(RADIANS({$lat})) * COS(RADIANS(g.latitude)) * COS(RADIANS(g.longitude) - RADIANS({$long})) + SIN(RADIANS({$lat})) * SIN(RADIANS(g.latitude)))) <= {$radius}
    GROUP BY u.`ID`

Ceci peut être optimisé en utilisant un filtre pré-agrégé "précoce". C'est-à-dire en appliquant le filtre WHERE sur le ON le plus tôt possible. Bien que cela puisse paraître "bizarre", cela peut être beaucoup plus rapide. Dans votre cas, cela ressemblerait à ceci :

SELECT u.id AS USERID
    FROM `GEO` g
    JOIN `LOCATIONS` l ON 
        (6371 * ACOS(COS(RADIANS({$lat})) * COS(RADIANS(g.latitude)) * COS(RADIANS(g.longitude) - RADIANS({$long})) + SIN(RADIANS({$lat})) * SIN(RADIANS(g.latitude)))) <= {$radius}
        AND l.`AREA` = g.`ID`
    JOIN `USER` u ON l.`ID` = u.`ID`        
    GROUP BY u.`ID`

• Notez que si vous souhaitez également sélectionner la distance, vous pouvez toujours l'indiquer dans la liste des champs de sélection, comme vous l'avez fait. DISTINCT vous n'en obtiendrez qu'un seul, alors que si vous utilisez GROUP BY vous pourriez concaténer toutes les distances
• Je recommande d'essayer les deux GROUP BY et DISTINCT car les différences de performances peuvent être très importantes et imprévisibles. (voir par exemple cette question )
• Je me demande simplement s'il ne serait pas plus efficace de précalculer des parties telles que ACOS(COS(RADIANS({$lat})) au lieu de le faire à la volée, y a-t-il une raison de le maintenir ainsi ?
• En outre, vous pouvez stocker les valeurs long / lat en radians pour une optimisation plus poussée.