MySQL:不要选择在其他联接中匹配的记录

Question

也许我的标题是错误的，但我想向您展示我的查询以及执行搜索的结果中存在的错误:

查询

select distinct
    U.id,
    U.first_name,
    U.last_name,
    C.from_user,
    case when C.to_user is null 
        then 99
        else C.to_user
    end as to_user,
   case when C.status is null 
        then 99
        else C.status
    end as connection_type,
    case when C2.from_user is null 
        then 99
        else C2.from_user
    end as from_user_2,
    C2.to_user as to_user_2,
    case when C2.status is null 
        then 99
        else C2.status
    end as connection_type_2,
    ( 3959 * acos(
        cos(radians(19.3901580))
        * cos(radians(L.latitude))
                * cos(radians(L.longitude) - radians(-99.1733260))
                + sin(radians(19.3901580))
                * sin(radians(L.latitude)))
    ) AS distance
from users U
left join connections C on C.from_user = U.id
left join connections C2 on C2.to_user = U.id
left join locations L on L.user_id = U.id
where U.id != 10
#group by U.id
having distance < 70
    #and (connection_type_2 = 1 or connection_type_2 = 99)
   #and from_user_2 != 10
   #and (to_user != 10 or connection_type != 3)
   #and to_user != 10
order by distance asc

结果

如果我取消注释查询中注释的行，这就是结果

用户 ID:10

如果to_user = 10并且from_user idconnection_type = 3我不需要获取已经匹配的用户在这种情况下，这意味着 id 30 的用户将不会被选择。

所以我有这个问题，当然这个搜索功能有很多规则，但这是完成它的最后一步!

编辑:

对请求的一些解释

• 用户之间的状态为 3 时已连接
• 传入请求是指它们之间的状态为 1 的情况，如下所示:
  • from_user:20 to_user:10 status:1(用户 10 有来自 20 的传入请求
  • from_user:10 to_user:20 status:1(用户 20 有来自 10 的传入请求
• 传出请求是指它们之间的状态为 1 的情况，例如:
  • from_user:20 to_user:10 status:1(用户 10 已向 20 发送请求
  • from_user:10 to_user:20 status:1(用户 20 已向 10 发送请求
• 当状态 from_user to_user 为 2 时，用户被阻止:
  • from_user:20 to_user:30 status:2(用户 20 屏蔽了用户 30)

规则

获取:

• 我需要获取没有任何请求的用户，这意味着不在连接表中的用户
• 获取已向当前用户(本例中为 10 个)发送请求的用户。

不明白:

• 不要获取我已向他们发送邀请的用户
• 不要阻止用户

希望你能帮助我!

Answer 1

你可以试试这个

select * from (
   select id, first_name, last_name, (select  3959 * acos(
        cos(radians(19.3901580))
        * cos(radians(L.latitude))
                * cos(radians(L.longitude) - radians(-99.1733260))
                + sin(radians(19.3901580))
                * sin(radians(L.latitude)))
                from locations l where l.userid = u.id ) as distance  from  users u
  where 
  (
   id in (select from_user from connections where to_user=10 and status=1)
    or (id not in (select from_user from connections where to_user=10) and id not in (select to_user from connections where from_user=10) )
  ) 
  and id !=10
  and id not in (select to_user from connections where from_user =10 and status=2)
  and id not in (select to_user from connections where from_user=10 and status=1) 
  and id not in (select to_user from connections where from_user=10 and status=3)
  and id not in (select from_user from connections where to_user=10 and status=3)
) a
where distance < 10;

尚未优化，但您需要先获得正确的结果。

添加与当前用户的连接状态

select *,
   (select status from connections l where (l.to_user=a.id
          and l.from_user = 10
            or l.from_user = a.id  and l.to_user=10 
     limit 1)  as status
  from (
   select id, first_name, last_name, (select  3959 * acos(
        cos(radians(19.3901580))
        * cos(radians(L.latitude))
                * cos(radians(L.longitude) - radians(-99.1733260))
                + sin(radians(19.3901580))
                * sin(radians(L.latitude)))
                from locations l where l.userid = u.id ) as distance  from  users u
  where 
  (
   id in (select from_user from connections where to_user=10 and status=1)
    or (id not in (select from_user from connections where to_user=10) and id not in (select to_user from connections where from_user=10) )
  ) 
  and id !=10
  and id not in (select to_user from connections where from_user =10 and status in (1,2,3)) 
  and id not in (select from_user from connections where to_user=10 and status=3)
) a
where distance < 10