How to obtain a relevance score in a table

Question

I have a mysql table that has typetags for games, like

table game_typetags:

   name   typetag
--------- --------
 game#1    sports
 game#1    soccer
 game#2    race
 game#2    sports

and another table games like

   name   playcount 
--------- --------- 
 game#1      10      
 game#2      8

And I want to list relevant games (score them with number of common typetags) and sort with playcount.

So I'm trying to get such an array in PHP:

function getSimilarGames($typetags_array)
{
$pdo->query("SELECT * FROM games COUNT (*) AS ???? ORDER BY games.playcount DESC");
}

So that when I call this function with $typetags = [race,sports] , it should return

name   score
____   _____
game#2   2
game#1   1

What query should I use for this? Thanks !

score 0 · Answer 1 · answered Feb 24 '13 at 11:31

I do not agree with your database design, you should try to normalize it a bit but here is code that would work on your design:

SELECT games.name, COUNT(1) score
FROM games
INNER JOIN game_typetags ON game_typetags.name = games.name
WHERE game_typetags.typetag IN ('race', 'sports')
GROUP BY games.name
ORDER BY score DESC

I suggest a design like so;

game
-------
id integer pk
name varchar

typetag
-------
id integer pk
tagname varchar

game_typetags
-------
game_id int pk
typetag_id int pk

With this design you join together your tables using ID's instead of using strings everywhere.

A query to get the data you want from this could look like this, note that you could even use the tag-ids to make it tad faster (working on primary key in the database instead of string comparison in the where clause)

SELECT game.id, game.name, game.playcount, COUNT(1) score
FROM game
INNER JOIN game_typetags gtt ON gtt.game_id = game.id
INNER JOIN typetag tt ON tt.id = gtt.typetag_id
WHERE tt.typetag IN ('race', 'sports')
GROUP BY game.id
ORDER BY score DESC

Thanks. This was actually my attempt to normalize the tables by the way :) — marvin, Feb 24 '13 at 12:08

score 0 · Answer 2 · answered Feb 24 '13 at 12:09

And I figured out a solution to this problem, as follows:

SELECT name, SUM(matches) as relevance_score FROM
(
SELECT
  game_typetags.*,
    (typetag =  'race'  )
+    (typetag =  'sports'  )
    AS matches
FROM game_typetags HAVING matches>0
ORDER BY matches DESC 
) a GROUP BY name ;

How to obtain a relevance score in a table

2 Answers2