带有隐私条件检查的查询中的 mysql 查询 [英] mysql query within a query with privacy condition check

问题描述

我有 3 张桌子.

myMembers
------------------------------------
id | username | privacy
------------------------------------
1  | userA    | 0
2  | userB    | 1
3  | userC    | 0
4  | userD    | 1

following
--------------------------------
id  | user_id  | follower_id
--------------------------------
1   | 2        | 1

posts
-------------------------------------
id  |  userID | username   | statusMsg
--------------------------------------
1   |  4      | userD    | Issac Newton is genius
2   |  2      | userB    | Newton Saw apple
3   |  3      | userC    | Newtonian Physics
4   |  1      | userA    | Calculus came from Sir Newton

有一个搜索字段.当登录用户在表 'posts' 中搜索 'keyword' 时，我想忽略那些将其隐私设置为 '1' 并且搜索者未关注用户 B 的用户的结果.

There is a search field. When a logged in user searches for 'keyword' in table 'posts', I want to omit results from those users who has set his privacy to '1' and WHERE searcher is not following user B.

查询在逻辑上应该这样做.

The query should logically do this.

SELECT * from posts WHERE (match the keyword) 
            AND ( 
                if (poster's privacy (which is set in myMembers)==1){
                    if (seacher is following poster){
                        select this post
                    }
                }
                   else { select this post
                }
             )
                   LIMIT results to 5 rows

所以对于关键字牛顿"，如果用户 A 正在搜索，则应返回帖子"中的第 2、3、4 行.如果 userD 正在搜索，则只应返回posts"中的第 1、3 和 4 行，

So for a keyword "Newton", if userA is searching, rows 2,3,4 from 'posts' should be returned. if userD is searching, only rows 1, 3 and 4 from 'posts' should be returned,

基于隐私和关注标记以供将来搜索:mySql 中 WHERE 子句中的 IF 条件

based on privacy and following Tagging for future searches: IF condition within WHERE Clause in mySql

推荐答案

请试试这个查询(也在 SQL 小提琴):

Please, try this query (also on SQL Fiddle):

SELECT p.id, p.user_id, m.username, m.privacy,
       searcher.username "Searcher", p.status_msg
  FROM posts p
  JOIN members m ON m.id = p.user_id
  LEFT JOIN following f ON p.user_id = f.user_id
  JOIN members searcher ON searcher.username = 'userA'
 WHERE (m.privacy = 0 OR (m.privacy = 1 AND f.follower_id = searcher.id)
                      OR m.id = searcher.id)
   AND p.status_msg LIKE '%New%'
 ORDER BY p.id
 LIMIT 5;

我从 posts 表中删除了 username 字段，因为它是多余的.此外，我命名的表和列略有不同，因此查询可能需要对架构进行外观更改.

I removed username field from posts table, as it is redundant. Also, I named tables and columns slightly different, so query might need cosmetic changes for your schema.

WHERE 子句中的第一行是您要查找的内容，它按以下顺序选择帖子:

The first line in the WHERE clause is the one that you're looking for, it selects posts in the following order:

1. 来自没有隐私的成员的第一篇帖子；
2. 然后是来自当前searcher的成员的帖子；
3. 最后是成员本人的帖子.

<小时>

此查询使用原始标识符:

This query is using original identifiers:

SELECT p.id, p.`userID`, m.username, m.privacy,
       searcher.username "Searcher", p.`statusMsg`
  FROM posts p
  JOIN `myMembers` m ON m.id = p.`userID`
  LEFT JOIN following f ON p.`userID` = f.user_id
  JOIN `myMembers` searcher ON searcher.username = 'userD'
 WHERE (m.privacy = 0 OR f.follower_id = searcher.id OR m.id = searcher.id)
   AND p.`statusMsg` LIKE '%New%'
 ORDER BY p.id
 LIMIT 5;

<小时>

编辑 2:

为了避免重复，以防 posts 表中的用户有多个关注者，加入和过滤条件应按以下方式更改(在 SQL 小提琴):

To avoid duplicates in case there're several followers for the user from the posts table, join and filtering conditions should be changed the following way (on SQL Fiddle):

SELECT p.id, p.user_id, m.username, m.privacy,
       searcher.username "Searcher", p.status_msg
  FROM posts p
  JOIN members m ON m.id = p.user_id
  JOIN members searcher ON searcher.username = 'userC'
  LEFT JOIN following f ON p.user_id = f.user_id
   AND follower_id = searcher.id
 WHERE (m.privacy = 0 OR (m.privacy = 1 AND f.id IS NOT NULL)
                      OR m.id = searcher.id)  
 ORDER BY p.id
 LIMIT 5;

这篇关于带有隐私条件检查的查询中的 mysql 查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！