By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE users
(`id` varchar(5), `name` varchar(5), `longitude` varchar(11), `latitude` varchar(11))
;
INSERT INTO users
(`id`, `name`, `longitude`, `latitude`)
VALUES
('1', 'Mark', '-82.347036', '29.6545095'),
('2', 'John', '-82.357036', '29.665095'),
('3', 'Paul', '-82.367036', '29.645095'),
('4', 'Dave', '-82.337036', '29.675095'),
('5', 'Chris', '-82.437036', '29.575095'),
('6', 'Manny', '-82.538036', '29.745095'),
('7', 'Fred', '-82.638036', '29.346095')
;
Records: 7 Duplicates: 0 Warnings: 0
CREATE TABLE matches
(`id` int, `sender` int, `receiver` int, `status` int)
;
INSERT INTO matches
(`id`, `sender`, `receiver`, `status`)
VALUES
(1, 3, 4, 0),
(2, 1, 5, 1),
(3, 6, 3, 1),
(4, 2, 6, 0),
(5, 2, 1, 0),
(6, 6, 1, 1)
;
Records: 6 Duplicates: 0 Warnings: 0
SELECT
id
, ( 3959 * acos( cos( radians(37) ) * cos( radians( latitude ) )
* cos( radians( longitude )
- radians(-122) ) + sin( radians(37) )
* sin(radians(latitude)) ) ) AS distance
FROM
users
HAVING distance < 5000
ORDER BY distance
DESC LIMIT 20
| id | distance |
|---|---|
| 1 | 2327.4584577672013 |
| 4 | 2327.4495050664714 |
| 2 | 2326.6182412310427 |
| 3 | 2326.6115541182535 |
| 5 | 2324.66187722644 |
| 7 | 2319.8830242716303 |
| 6 | 2314.456433247064 |
SELECT
a.id, a.distance
FROM
(SELECT
id,
ST_DISTANCE_SPHERE(POINT(- 82.337036, 29.645095), POINT(`longitude`, `latitude`)) / 1000 AS distance
FROM
users u
WHERE
id <> 1
HAVING distance < 5000
ORDER BY distance DESC
LIMIT 20) a
WHERE
a.id NOT IN (SELECT `sender` FROM (SELECT `sender` FROM matches UNION SELECT `receiver` FROM matches) t1
WHERE `sender` IN (SELECT `sender` FROM matches WHERE `receiver` = 1 AND status = 1
UNION SELECT `receiver` FROM matches WHERE `sender` = 1
))
ORDER BY a.distance ASC
| id | distance |
|---|---|
| 3 | 2.8991986256467865 |
| 2 | 2.946298180421104 |
| 4 | 3.335840468953696 |
| 7 | 44.20441197234838 |
SELECT `receiver` FROM matches WHERE `sender` = 1
| receiver |
|---|
| 5 |
SELECT `sender` FROM matches WHERE `receiver` = 1 AND status = 0
| sender |
|---|
| 2 |
SELECT `sender` FROM matches WHERE `receiver` = 1 AND status = 1
| sender |
|---|
| 6 |
SELECT DISTINCT `receiver` FROM (SELECT `receiver` FROM matches WHERE `sender` = 1
#UNION DISTINCT SELECT `sender` FROM matches WHERE `receiver` = 1 AND status = 1
UNION DISTINCT SELECT `sender` FROM matches UNION ALL SELECT `receiver` FROM matches) a
| receiver |
|---|
| 5 |
| 3 |
| 1 |
| 6 |
| 2 |
| 4 |
SELECT `sender` FROM (SELECT `sender` FROM matches UNION SELECT `receiver` FROM matches) t1
WHERE `sender` IN (SELECT `sender` FROM matches WHERE `receiver` = 1 AND status = 1
UNION SELECT `receiver` FROM matches WHERE `sender` = 1
)
| sender |
|---|
| 6 |
| 5 |
SELECT
a.id
,a.distance
FROM
(Select
id,
st_distance_sphere(POINT(-82.337036, 29.645095 ), POINT(`longitude`, `latitude` ))/1000 as distance
FROM
users u
WHERE id <> 1
HAVING distance < 5000
ORDER BY distance
DESC LIMIT 20) a
WHERE
a.id not in (SELECT `receiver` FROM matches WHERE `sender` = 1)
OR a.id in (SELECT `sender` FROM matches WHERE `receiver` = 1 AND status = 0)
OR
a.id NOT IN ( SELECT `sender` FROM matches UNION ALL SELECT `receiver` FROM matches )
ORDER BY a.distance ASC
| id | distance |
|---|---|
| 3 | 2.8991986256467865 |
| 2 | 2.946298180421104 |
| 4 | 3.335840468953696 |
| 6 | 22.373725436534478 |
| 7 | 44.20441197234838 |