Efficient SQL Query

toastyman

toastyman

toastyman

Associate
Joined
30 Dec 2005
Posts
415
Greetings all,

I'm building a community based website for a client, but they've asked me to create something which i'm not sure on how to go about doing...

Basically, you create friends on this website. These details are stored in two tables...

User
User ID
First Name
Last Name
etc..

Friends
Link ID
User ID 1
User ID 2

Easy enough so far!

The client has asked me to build a feature specifying the following:
When the user clicks on another user's profile, and they're not directly friends, work out if they know any of your other friends, to calculate the chance that you might know them..

So for example...
Rich is friends with Phil, and Phil is friends with Dan. However, Rich is not currently friends with Dan. When Rich goes on Dan's profile, it needs to go through all Rich's friends, to see if they know Dan. The theory is, it should find that Phil is friends with Dan.

Now I can do this using PHP, no problem. My idea was to have a loop, and just go through each one of Rich's friends, running an SQL query to see if they know Dan. However, a query per user... if Rich has 400 friends, that is not exactly an ideal solution.

Is there a way of doing this using 1 SQL query, or another way of doing it efficiently?

Any thoughts would be hugely appreciated! :)
 
What is your current query [if you used the loop]?

It might easily be modified to return all the results to PHP.

Still getting to grips with SQL myself, so not much use to you until I see your current query!

EDIT: Facebook has a similar thing, when you view someone's profile it tells you your mutual friends, if any.
 
well you need 2 queries. one to return a list of rich's friends. and another to return a list of dan's friends. store the results in php arrays and do the comparing in php?
 
I haven't actually written the query yet, as i'm still in the 'working it out' stage. :D

Here's my psuedo query (obviously missing the Inner Join)

SELECT `user_id` FROM `users` WHERE (`friends`.``User ID 1` = 'Phil' AND `friends`.``User ID 2` = 'Dan') OR (`friends`.``User ID 1` = 'Dan' AND `friends`.``User ID 2` = 'Phil')

My prediction is it's going to get a lot more complicated!


Yep, Facebook has a similar thing, but what this client has requested has a much more intuitive front end :) I really should have just mentioned the Facebook Mutual Friends thing instead of waffling on with examples etc :p
 
One thing - remove the useless 'Link ID' column from the Friends table and add a compound key on the 2 remaining columns instead :)
 
marc2003 said:
well you need 2 queries. one to return a list of rich's friends. and another to return a list of dan's friends. store the results in php arrays and do the comparing in php?
Click to expand...
Some of the 'advise' given on this forum is scary.

This SQL works, I'm sure you could make it nicer, but I've just got in from a 15 hour day and my brain isn't working :(

Code: 
SELECT DISTINCT Users.*
FROM Users INNER JOIN Friends ON (Users.UserID = Friends.UserD1 OR Users.UserID = Friends.UserID2)
WHERE Users.UserID <> 1
AND (Friends.UserID1 = 1 OR Friends.UserID2 = 1)
AND Users.UserID IN
(
    SELECT Users.UserID
    FROM Users INNER JOIN Friends ON (Users.UserID = Friends.UserID1 OR Users.UserID = Friends.UserID2)
    WHERE Users.UserID <> 2
    AND (Friends.UserID1 = 2 OR Friends.UserID2 = 2)
)

Replace the 1 and 2 with your two users IDs
 
Wow thanks all! I think i'm going to try all the solutions given, run benchmarks and work out which one executes the fastest. My guess is it's going to be the SQL given by Mickey, but it can't hurt to test :)

Thanks for the tip Beansprout! I'll have to research how to create a compound key in MySQL, and all will be good.

Cheers all!
 
marc2003 said:
well you need 2 queries. one to return a list of rich's friends. and another to return a list of dan's friends. store the results in php arrays and do the comparing in php?
Click to expand...

Why would you want to do that? You can do the entire thing in one SQL query or stored proceedure and no need to do it on the page :confused:

How many friends would you need to bring back?

If its only a couple then use Mickey's way... if it's a lot then use a stored procedure with temporary tables would be easier?
 
I've wrote a few for clients for things like 'Other People Bought','Other People Stayed Here'

This is pretty much along the same lines, I'll see if I can adapt something for you if you still need it?
 
If you want an efficient query don't use Users.*, name the fields you want. Using the * causes it query the masterdb for the list of fields
 
chesterstu said:
If you want an efficient query don't use Users.*, name the fields you want. Using the * causes it query the masterdb for the list of fields
Click to expand...

I think anyone with SQL knowledge knows not to use * without decent reason.
I'm sure Mickey was using it as reference to the fact we don't know all his fields etc
 
Open Query Analyser or something similar and have a look at the following

declare @userid int
set @userid = 1

declare @users table (userid int identity, username char(10))
insert into @users
select 'rich'
insert into @users
select 'phil'
insert into @users
select 'dan'
insert into @users
select 'jeff'
insert into @users
select 'ted'

declare @friends table (id int identity, userid int, friendid int)
insert into @friends
select 1,2
insert into @friends
select 2,3
insert into @friends
select 2,1
insert into @friends
select 2,4


declare @myfriends table (userid int, username char(10))
insert into @myfriends
select userid,username from @users
where userid in (select friendid from @friends where userid = @userid)

declare @possfriends table (userid int, username char(10))
insert into @possfriends
select userid,username from @users
where userid in (select userid from @users
where userid in (select friendid from @friends where userid = (select userid from @myfriends))) and userid <> @userid


--show my friends only
--select * from @myfriends

--show my possible friends only
--select * from @possfriends

--show all
select * from @myfriends
union all
select * from @possfriends
order by userid
Click to expand...

Obviously this could be turned into an SP, where you just pass it the current userid and it returns friends/possfriends.
 
Last edited:
You must log in or register to reply here.
Back
Top Bottom