I'd appreciate your help on this please :

开发者_Python百科

I've got a table which logs when an user (id_user) where at an event (meeting, concert..) (id_event) and his score for this event (long story). Here is my table:

CREATE TABLE `Leaderboards` (
  `id` mediumint(9) NOT NULL AUTO_INCREMENT,
  `id_event` mediumint(8) NOT NULL,
  `user_id` mediumint(9) NOT NULL,
  `score` smallint(4) NOT NULL DEFAULT '0',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 COLLATE=utf8_bin;

Now, I'd like to suggest to an user all the users which have been to at least one event in common, and sort it by users which have been to a lot of events in common.

I might have an idea to do it in php after a row request, but is there a smarter way to do it directly through MySQL?

Thank you for your answers!

---

I think I might have an answer for you

SELECT leader1.user_id, leader2.user_id, COUNT(leader1.id) AS num
FROM       Leaderboards AS leader1
INNER JOIN Leaderboards AS leader2 ON (
    leader1.id_event = leader2.id_event AND
    leader1.user_id != leader2.user_id
)
GROUP BY leader1.user_id, leader2.user_id
ORDER BY num DESC

This will select the ids of the users who at least one event in common, as well as the number of events. Finally it will order by the number of events in common (num) descending. If you would like to do it for a specific user, add in a where clause such as below

WHERE leader1.user_id = :user_id

I also believe this query if pretty efficient as well. Let me know if this is not quite what you need and I will see if I can make adjustments.

---

For user #1234

SELECT user_id, COUNT(1)
FROM   Leaderboards
WHERE  user_id <> 1234
AND    id_event IN (
       SELECT id_event FROM Leaderboards WHERE user_id = 1234
)
GROUP BY user_id
ORDER BY COUNT(1) DESC

---

I think the following query would be a fit:

select count(event) as event_count, user_id from
    (select distinct id_event as event, 1 as left_userid from `Leaderboards` where user_id = 1) as leftbl,
    Leaderboards
where
    event = Leaderboards.id_event AND
    left_userid <> Leaderboards.user_id
group by
    user_id
order by
    event_count desc

First the subselect determines all unique events for the given user (in this case 1) and then joins this with the Leaderboards table. Counting the events and grouping by user does the rest.

---

I would go for the inner join one. As far as I know it causes less overhead on the server, especially when considering the number of rows in a leaderboard would be huge on a popular web application/website.