Mysql – How to get data from MySQL table based on a junction table

MySQL

I've been trying to tackle this problem but couldn't do it properly, any help is really appreciated.

This is the Distance calculation query based on latitude, longitude and distance in KMs

SELECT *
FROM (
         SELECT *,
                (
                        ((acos(sin((27.6677608 * pi() / 180)) * sin((activities.latitude * pi() / 180)) +
                               cos((27.6677608 * pi() / 180)) * cos((activities.latitude * pi() / 180)) *
                               cos(((68.8543843 - activities.longitude) * pi() / 180)))) * 180 / pi()) * 60 *
                        1.1515 * 1.609344
                    )
                    as distance
         FROM `activities`
         WHERE is_public = true
           AND (
                     DATEDIFF(end_time, start_time) > 0
                 OR
                     TIME_TO_SEC(TIMEDIFF(TIME(end_time), TIME(start_time))) > 10
             )
     ) markers
WHERE distance <= 22

This is the activity related table ER diagram
enter image description here

Now what i have been trying to do is that the user provides latitude, longitude and distance from which i calculate activities within the distance, lat/longs provided by the user.

PROBLEM => problem is that i want to get activities which are not only close to user's lat/long and distance but also matching certain interest_id from activity_interests table.

SCENARIO => for instance, i provide these details as payload

{
    "latitude": "27.6677608",
    "longitude": "68.8543843",
    "distance": 7,
    "interests": [1,2,3]
}

Now i want Activities which have all above payload Interest ids present in the Activity_interests table and still do the distance calculation for which i have provided the above SQL query.

MySQL version 8.0 is used

Thank you, and i hope i explained it well.

Best Answer

You can do something like this:

SELECT a.*
   ,st_distance_sphere(POINT(68.8543843, 27.6677608 ), POINT(a.`longitude`, a.`latitude` ))/1000   as distance
FROM `activities` a INNER JOIN `activity_interests` ai ON a.id = ai.activity_id 
WHERE is_public = true
   AND (
             DATEDIFF(end_time, start_time) > 0
         OR
             TIME_TO_SEC(TIMEDIFF(TIME(end_time), TIME(start_time))) > 10
     ) 
     AND ai.interest_id IN (1,2,3)
HAVING distance <= 22

As long as you don't provide data, I can only hint at how it would look like.

SELECT a.*,st_distance_sphere(POINT(68.8543843, 27.6677608 ), POINT(`longitude`, `latitude` ))/1000   as distance
FROM `activities` a INNER JOIN `activity_interests` ai ON a.id = ai.activity_id 
WHERE is_public = true
   AND (
             DATEDIFF(end_time, start_time) > 0
         OR
             TIME_TO_SEC(TIMEDIFF(TIME(end_time), TIME(start_time))) > 10
     ) 
     AND SUM(ai.interest_id =1) + SUM(ai.interest_id =2) + SUM(ai.interest_id =3) = 3
GROUP BY a.id,distance
HAVING distance <= 22
Related Question