Calculate "most popular" ids in multiple database columns

Question

On my site (aimed at PS3 gamers), users can select the 3 games they're currently playing. I will then work out which games are the most popular, and list the top 5 based on active users.

I have 3 columns in my users table called game1_id, game2_id and game3_id. These values are relational to the id column in another table called game, which contains all of the information on the games such as it's title.

How would I go about tallying up these totals and returning the top 5 most active games?

Here is my model function thus far:

function get_5_popular_games()
{
    $this->db->select('user.game1_id, user.game2_id, user.game3_id');
    $this->db->from('user');
    $query = $this->db->get();

    if ($query->num_rows() > 0)
    {
        return $query->result();
    }
}

I assume I need to somehow tally up how many times a games id is found in any of the three columns (game1_id, game2_id, game3_id), but I'm not entirely sure how to go about it?

[EDIT] AND HERE IS THE FINISHED FUNCTION.

function get_popular_games($limit)

{

    $this->db->select('count(*) as popularity, game.title');
    $this->db->from('user_game');
    $this->db->join('game', 'game.id = user_game.game_id');
    $this->db->group_by('game_id');
    $this->db->order_by('popularity', 'desc');
    $this->db->limit($limit);
    $query = $this->db->get();

    if ($query->num_rows() > 0)

    {

        return $query->result();

    }

}

score 1 · Answer 1 · answered Feb 12 '12 at 00:40

1

I think a better design would be to have a many-to-many table relating the user_id with the game_id, resulting in each user having three rows for their favorite games.

answered Feb 12 '12 at 00:40

iDifferent

2,190
1
14
19

Yep, good thinking. I'll redesign my database now and do it that way. – Prefontaine Feb 12 '12 at 00:42
1

This is not an answer to the question asked, and shouldn't be posted as one. It belongs as a comment to the original question instead. (If you're not sure what the difference is, see the [FAQ](http://stackoverflow.com/faq) for an explanation of what does (and doesn't) constitute an answer. – Ken White Feb 12 '12 at 00:43

score 1 · Accepted Answer · edited May 23 '17 at 10:34

Instead of having 3 columns, use a separate table to store the favorite games for each user. Then you could use a simple query like the following to get the top 5 games:

SELECT game_id, count( * ) as popularity
FROM users_favorite_games
GROUP BY game_id
ORDER BY popularity DESC
LIMIT 5;

Edit
Somebody didn't like this answer, so a little more context may be in order...

You could do what you want without changing your schema. Your query would look similar to the following:

SELECT game_id, count( game_id ) AS Popularity
FROM (
    SELECT id, game1_id AS game_id
    FROM user
    UNION SELECT id, game2_id
    FROM user
    UNION SELECT id, game3_id
    FROM user
    ) AS user_games
GROUP BY game_id
ORDER BY Popularity DESC;

However, the example in the question shows that you are using CodeIgniter's ActiveRecord, which does not support UNION. So you would end up with a more complicated query and some extra hacking around in your method. Not exactly desirable.

Perfect. Works well. I'll edit my OP now and include the solution function. Thanks! — Prefontaine, Feb 12 '12 at 01:03

Calculate "most popular" ids in multiple database columns

2 Answers2