具有隐私条件检查的查询中的mysql查询

Question

I have 3 tables.

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

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

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,

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

Answer 1

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;

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.

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

1. First posts from members without privacy;
2. Then posts from members that are followed by the current searcher;
3. Finally, posts of the member himself.

---

EDIT:

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;

---

EDIT 2:

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;