完了

MySQL query to show mutual friend suggestions

I am developing a website and have a query to show mutual friend suggestions, however the query isn't working perfectly. These friend suggestions will each have a button that when clicked will send a friend request to the user. The query that I have now shows mutual friend suggestions but is also displaying members who the user has already sent a request to. I do not want these people in the list, since a request has already been sent from the user viewing this list.

`friends` table structure with sample data

---------------------------------------

id, user_id, friend_id, status, date

149 1253343 1243522 0 1567894334

150 1243522 1253343 1 1567894334

151 1262117 1441712 2 1578241277

152 1441712 1262117 2 1578241277

When a user requests a friendship, two rows are inserted into the database, one for the requester and one for the requested. The requester row has a status of 1 while the requested row has a status of 0. Any status 2 means a confirmed friendship between two users.

Here is the current query that needs to be modified to not show rows with a 0 or 1. In other words it shouldn't return users who have already sent or received a request from the user. The query should only return users who have not been sent a request yet from the user or have not sent one to this user. This database query stuff is a little over my head. I'm not sure if there is a query generator program that would help me with these complicated queries but for now I am requesting assistance from the developer community. Good luck!

SELECT

a.friend_id,

COUNT(*) as relevance,

GROUP_CONCAT(a.user_id ORDER BY a.user_id) as mutual_friends

FROM

friends a

JOIN

friends b

ON (

b.friend_id = a.user_id

AND [login to view URL] = 2

AND b.user_id = ".$_SESSION['user_id']."

)

LEFT JOIN

friends c

ON

(

c.friend_id = a.friend_id

AND [login to view URL] = 2

AND c.user_id = ".$_SESSION['user_id']."

)

WHERE

[login to view URL] = 2

AND

c.user_id IS NULL

AND

a.friend_id != ".$_SESSION['user_id']."

GROUP BY

a.friend_id

ORDER BY

RAND() LIMIT 5

スキル: MySQL, ソフトウェアアーキテクチャ

さらに表示: edit csv mysql query, asp mysql query, 2013 lost connection mysql query, mysql query report, php mysql query table, mysql query record bash, mysql query csv file, write mysql query, optimize mysql query software, mysql query php table css, php mysql query format table, mysql query search, cacti custom mysql query, google map mysql query results, php mysql query category drop, unowned service requests help desk template sharepoint, mysql query remote database, mysql query find children parent, write javascript function mysql query

採用者について:
( レビュー1件 ) Bethel, United States

プロジェクトID: #21182606

アワード:

NabeelAbid22

Hey, I will rewrite or modify your query to work correctly. Right now I’m on mobile so I can’t test your query but I have go through your table structure and query you have written. If I will get this job, then I wi もっと

$25 USD 1日以内
(6レビュー)
3.5

5人のフリーランサーが、このジョブに平均$22で入札しています

yuriyes43

Hi, are you looking for an Real MySQL Expert? If yes, you're in RIGHT place and WELCOME! High-quality and Fast-delivery is promised! As a highly skilled full stack developer, I have rich experience in MySQL. I am very もっと

$20 USD 1日以内
(53件のレビュー)
6.6
DianaBorisov

Hi, Got your project requirements here. Thanks for your sharing it. I have been doing back-end and front-end developer with more than 8 years My main skill: PHP(Laravel, Codegniter, Yii, Wordpress), NodeJs, JSP, JQu もっと

$20 USD 7日以内
(26件のレビュー)
5.5
baidyamadhu007

I am a Php developer.I also expert in wordpress.I have been worked during 7 yrs in Jquery/Javascript and core php also.I have been worked on that field during 7 years of experience with that filed. I am able to fix もっと

$25 USD 0日以内
(3件のレビュー)
3.3
riksubhraray

I can start the work as soon as you approve me the same and submit the deliverables in agreed timeline with quality and optimized coding pattern. I can do the optimized query as per your requirements as I have implemen もっと

$19 USD 1日以内
(0件のレビュー)
0.0