I have a mysql query that fetches users within a certain distance of given point
SELECT users2.*
FROM users2
LEFT JOIN user_location2
ON user_location2.uid = users2.id
WHERE ( 3959 * acos( cos( radians(28.547800068217) ) * cos( radians( `lat` ) ) * cos( radians( `lon` ) - radians(-82.726205977101) )
+ sin( radians(28.547800068217) ) * sin( radians( `lat` ) ) ) ) <= 25
AND
ORDER BY time_stamp
I'm hoping to add in a 3rd table (user_like) to eliminate a lot of possible rows that shouldn't be included in the result.
Let's say the script is running for user_id = 88
[![Here's the state of the table][1]][1]
I'm having issues calling the third table or including it at all.
So basically users 89, 90 and 91 would fall under the location radius, but wouldn't be included in the result because user 88 already liked them.
Something like:
WHERE user_like.uid1 = 88 AND user_like.uid2 != (89,90,91)
Edit:
Here's the 3 table structures:
-users2
id int(11)
(other columns have been omitted because they are irrelevant in this case select *)
-user_location2
uid int(11)
lat double
lon double
time_stamp bigint(20)
-user_like
uid1 int(11)
uid2 int(11)
time_stamp bigint(20)
To clarify a bit more, I have a user sending up a request (let's say user 88) that should return all users within 25 miles of them (this part currently works). What I'm trying to add is the for the query to take into considering the user_like table. If user 88 already "liked" another user (uid1, uid2) then that should be removed from the query result because user 88 has already seen and liked that user.
See Question&Answers more detail:
os 与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…