php - 通过搜索关键字查找用户并返回结果与共同 friend 的订单

Question

我正在构建一个应用程序，我需要在其中进行高级用户搜索，例如高级社交网络搜索...

Table sl_users 
user_id | full_name | etc
Table sl_user_friends
friend_id | user_id_FK | user_friend_id | accept_status

我的查询是这样的

SELECT * FROM (

SELECT user_id , full_name ,
(SELECT COUNT( * ) 
FROM (

        SELECT CASE WHEN user_id_FK = users.user_id
        THEN user_friend_id
        ELSE user_id_FK
        END AS friendID
        FROM sl_user_friends
        WHERE user_id_FK = users.user_id
        OR user_friend_id = users.user_id
        )a
        JOIN (

        SELECT CASE WHEN user_id_FK =3
        THEN user_friend_id
        ELSE user_id_FK
        END AS friendID
        FROM sl_user_friends
        WHERE user_id_FK =3
        OR user_friend_id =3
        )b ON b.friendID = a.friendID) as mutial_friend

    FROM sl_users

    ) AS users

我想要这样的结果，但这个查询不起作用。

user_id | full_name | mutial_friend 1 ali 11 3 noman 10 4 xyz 9

if i am user 3 and 4 is my friend then i am also friend of 4 . this query is giving me error like

#1054 - Unknown column 'users.user_id' in 'field list' 

Answer 1

对您的数据库使用以下查询。未经测试，但我希望它对您有用。

select user_id,full_name from sl_users sl left join sl_user_friends slf on slf.user_id_FK=sl.user_id