Advanced Sorting (Procedure?)

[iLLin]

Basically what I am trying to do is do some advanced sorting by what a user has in common with another user. I will try to as simple as possible.

Doing a search for users with a name that starts with "A". I want to sort the results by what the users have in common with the person doing the search. For example "Friends in Common" (Like facebook). Doing one I could probably do a subselect or a count or something. But I also want to sort by Places they have in common. Normally you would have a primary sort, then a sort on that sort. Well I want to be able to sort by the "most" So if they have 5 friends and 10 locations in common, that will give me a sort of "15" for example. If they have 0 friends and 6 places in common, it will give me a "6". And then I want to sort by that number but also return what places and friends they have in common.

Does that make any sense?

Thanks,
Dennis

[iLLin]

Ok solved my first issue but I might as well get more complicated. Here is my query so far that allows me to sort by whats in common:


SELECT profile_general.first_name, profile_general.last_name, profile_general.military_rank, profile_general.city, profile_general.state, profile_general.postal_code, profile_general.profile_image, profile_general.id user_id, profile_settings.show_online, bases.base_name,
(SELECT COUNT(*) FROM linking_friends_new WHERE profile_id=profile_general.id AND friend_id IN(SELECT friend_id FROM linking_friends_new WHERE profile_id='1')) as friend_total,
(SELECT COUNT(*) FROM profile_past_bases WHERE profile_id=profile_general.id AND past_base IN(SELECT past_base FROM profile_past_bases WHERE profile_id='1')) as base_total FROM profile_general
LEFT JOIN profile_settings ON profile_general.id=profile_settings.profile_id
LEFT JOIN bases ON profile_general.current_base=bases.id
WHERE (profile_general.first_name LIKE '%ryan%' OR profile_general.last_name LIKE '%ryan%') AND profile_general.gender LIKE 'Male%' AND profile_general.id!='1'
ORDER BY (friend_total + base_total) DESC LIMIT 0, 10


Ok profile_id is subbed by the current user... Now what I want to do with this subquery:


(SELECT COUNT(*) FROM profile_past_bases
WHERE profile_id=profile_general.id AND past_base IN(SELECT past_base FROM profile_past_bases WHERE profile_id='1')) as base_total


I want to filter it by dates the user was there the same time as the other user. (WHERE date_start >= 'date' AND date_end <= 'date')... But its a count searching IN a set. Do I need to do sub selects for each start and end date in the where clause by current past_base ID? Just seems like too much sub queries.

Is there a better method in doing this?

Thanks