mysql - 避免搜索时双重不在选择

Question

我有一个搜索引擎。我不希望被阻止的用户参与此搜索。我有表 block :

id - autoincrement
user - who is blocking
block - who is blocked

和表用户。我有这个选择，它工作正常:

select c.nome, c.user, c.id, p.foto from users c 
left join profile_picture p on c.id=p.user
where (c.id not in (select `block` from block where user = '1')) //avoid double block select
and (c.id not in (select `user` from block where block = '1')) //avoid double block select
and (c.user like '%uk%' OR c.nome like '%uk%')

问题是，我能否避免这种双重不进入，首先避免被阻止的用户被搜索，其次避免被阻止的用户搜索谁阻止了他。我可以避免两侧都选择这种双 block 表吗？

Answer 1

您可以使用 UNION 将两个查询合并为一个查询

select c.nome, c.user, c.id, p.foto 
from users c 
left join profile_picture p on c.id=p.user
where c.id not in 
(
  select `block` as  from block where user = '1'
  union  
  select `user` as block from block where block = '1'
) 
and (c.user like '%uk%' OR c.nome like '%uk%')