Scoring for categories and subcategories

Question

I am currently using a point system that uses the query below to indicate who has the highest score in a category or any category directly below it (using the "links" and "links" links in the "category_relations" table).

SELECT u.name, pa.user_id, SUM(IF(pa.plus = '1', pa.points_amount, 0)) - SUM(IF(pa.plus = '0', pa.points_amount, 0)) AS points FROM points_awarded pa, Users u WHERE u.user_id = pa.user_id AND ( category_id = '" . $category_id . "' OR category_id IN ( SELECT links_to FROM category_relations WHERE links_from = '" . $category . "' ) ) GROUP BY user_id ORDER BY points DESC LIMIT 50 

What I now want to do is switch this query to search all categories for a specific user, rather than search all users for a specific category. Below is the query that I tried to use, but this only applies to points for the category, not the subcategories directly below.

 SELECT u.name, pa.user_id, pa.category_id, SUM(IF(pa.plus = '1', pa.points_amount, 0)) - SUM(IF(pa.plus = '0',pa.points_amount, 0)) AS points FROM points_awarded pa, Users u WHERE u.user_id = pa.user_id AND u.user_id = '" . $user_id . "' GROUP BY pa.category_id ORDER BY points DESC LIMIT 50 

Answer 1

Decision

From your sample queries, I was able to get a simplification of your table structure, which provided me with enough information to answer your question. You can use this solution:

 SELECT a.user_id, a.name, b.category_id, SUM(IF(d.plus = '1', d.points_amount, 0)) - SUM(IF(d.plus = '0', d.points_amount, 0)) AS points FROM Users a JOIN points_awarded b ON a.user_id = b.user_id JOIN ( SELECT links_from, links_to FROM category_relations UNION SELECT links_from, links_from FROM category_relations ) c ON b.category_id = c.links_from JOIN points_awarded d ON c.links_to = d.category_id WHERE a.user_id = $user_id GROUP BY a.user_id, a.name, b.category_id ORDER BY points DESC LIMIT 50 

Where $user_id is the parameter of the user identifier for the request.

---

Structure

Basically, a key component of this query is the way you select the category_relations table.

Since you want the sum of the points of subcategories (for each parent category) plus to correspond to their parent categories, we can bind vertically to the parent category through UNION and essentially make it a subcategory itself. Then it will be well reflected in GROUP BY and SUM .

Let's say we have some (simplified) data:

 Users ------------------ user_id | name 433 | Zane 

---

 points_awarded ------------------ user_id | category_id | plus | points_amount 433 | 1 | 1 | 785 433 | 2 | 1 | 871 433 | 3 | 1 | 236 433 | 4 | 0 | 64 433 | 5 | 0 | 12 433 | 7 | 1 | 897 433 | 8 | 1 | 3 433 | 9 | 0 | 48 433 | 10 | 1 | 124 433 | 14 | 0 | 676 

---

 category_relations ------------------ links_from | links_to 1 | 2 1 | 3 1 | 4 5 | 8 5 | 9 7 | 10 7 | 14 

---

To distinguish between a parent and a subcategory, the query makes a self-intersection join in the points_awarded table using two fields in the category_relations table.

If we just joined the category_relations table, as without UNION , our resulting join would look something like (simplified columns):

points_awarded ⋈ (links_from) category_relations ⋈ (links_to) points_awarded:

 category_id | points_amount | category_id | points_amount 1 | 785 | 2 | 871 1 | 785 | 3 | 236 1 | 785 | 4 | 64 5 | 12 | 8 | 3 5 | 12 | 9 | 48 7 | 897 | 10 | 124 7 | 897 | 14 | 676 

As you can see, the leftmost category_id is the parent category, and the rightmost category_id is the corresponding subcategory. We can easily group the second points_amount by the first category_id and SUM ...

But wait, we also need to include the parent categories' points_amount ! How can we get the first points_amount in the second points_amount ? It uses UNION .

Before we complete our self-intersection join, we will list the category_relations table to change it a bit:

 SELECT links_from, links_to FROM category_relations UNION SELECT links_from, links_from FROM category_relations 

Which then leads to:

 category_relations (subselected) ------------------ links_from | links_to 1 | 1 <-- parent category 1 | 2 1 | 3 1 | 4 5 | 5 <-- parent category 5 | 8 5 | 9 7 | 7 <-- parent category 7 | 10 7 | 14 

If we essentially made each parent category a subcategory itself. We use the result of this in our mix, which then should produce:

 category_id | points_amount | category_id | points_amount 1 | 785 | 1 | 785 1 | 785 | 2 | 871 1 | 785 | 3 | 236 1 | 785 | 4 | 64 5 | 12 | 5 | 12 5 | 12 | 8 | 3 5 | 12 | 9 | 48 7 | 897 | 7 | 897 7 | 897 | 10 | 124 7 | 897 | 14 | 676 

Here NOW the parent category is counted in SUM , and now we can group the first category_id by adding up the second points_amount (ignore the first points_amount on this because it does not matter), giving you the desired result.

Answer 2

I am sure there is an easier way to do this by joining the category_relations table, but it’s hard not to see visual examples of tables ... I think this should work:

 SELECT u.name ,pa.user_id ,pa.category_id, SUM(IF(pa.plus = '1', pa.points_amount, 0)) - SUM(IF(pa.plus = '0',pa.points_amount, 0)) AS points FROM points_awarded pa JOIN Users u USING (user_id) WHERE category_id IN( SELECT links_to FROM category_relations WHERE links_from = ( SELECT category_id FROM points_awarded WHERE user_id = '" . $user_id . "' ) ) GROUP BY pa.category_id ORDER BY points DESC LIMIT 50 

You did not see the subcategory in your above example because you did not include entries from the category_relations table. Instead of searching for related categories by the requested category_id, we use a sub-subquery to find all category_id associated with the user. Give this snapshot, and if it is not, send a few small snippets showing the table data.