1

I have a categories table, which have two columns, id and name.

I have a types table, which have three columns, id, category_id and points.

I have a products table, which have three columns, id, type_id and customer_id.

This means each product has a type, and each type has a category. One category has many types, one type has many products.

I am using MySQL and PHP.

From PHP, given a customer_id, I want to know how much points that customer has in each category separately (and display a table to the user, for example).

My solution: I would first do a query to find what are all category ids, then for each category_id I would find all types inside that category (that is, all types with the given category_id); then for each type_id I would count how many products have both that type_id and the given customer_id, and I would multiply that amount for the points of the given type_id. Adding all the results for each type_id, I have in the end the total points the given customer has on that category_id. And as I said, I would have to do this for each category_id. My solution will work, but seems to me that it would be VERY slow. I am looking for advice on how to solve this problem in a better way - with less queries, or faster queries, or both. The way it is now, it can potentially do as many queries as the amount of categories.


Example:

/* TABLE: categories */

name           id
A              1
B              2
C              3

/* TABLE: types */

points         id          category_id
1000             1                1
500              2                1
200              3                3
50               4                2
20               5                3
1                6                3

/* TABLE: products */

id           type_id          customer_id
1               2                   13
2               4                   13
3               5                   13
4               6                   13
5               5                   13
6               3                   13
7               4                   13
8               5                   13
9               6                   13

If the given customer_id is 13, I want get something like the following:

500 total points in category A 100 total points in category B 262 total points in category C

1. What is a good way to do this without changing how my database is structured?

2. Is there a good way to change my database design to allow a better way to do this? (of course I have other fields in the tables that I omitted for simplicity).

3
  • Learn about JOINs and GROUP BY. It can be done with a single query for one user (and even for all users at the same time if you ever needed that).
    – jkavalik
    Oct 1, 2015 at 18:45
  • I'm sure the query you have attempted has some JOINs and a GROUP BY in it. If you post it, we may be able to help you improve it if it doesn't do what you expect it to.
    – mustaccio
    Oct 1, 2015 at 18:46
  • Thanks, with Max Vernon's answer I could understand perfecly how JOINs and GROUP BYs fit in my problem. Thanks for all the help.
    – Pedro A
    Oct 4, 2015 at 3:37

1 Answer 1

5

Your question shows you are thinking in a procedural way, however you need to think in a "set-based" way.

The following query may help you understand this better:

SELECT p.customer_id
    , c.name
    , sum(t.points)
FROM products p
    INNER JOIN types t ON p.type_id = t.id
    INNER JOIN categories c ON t.category_id = c.id
GROUP BY p.customer_id
    , c.name;

Essentially, this should show you a list of customer_id, category name, and the total points each customer has in each category. It accomplishes this through the use of an aggregate function (SUM) along with the GROUP BY clause.

(please take a look at my answer here for information about naming your ID columns)

1
  • 1
    Awesome!! This worked perfectly. And I did not just copy-paste, I learned about INNER JOIN and GROUP BY so I could understand what I'm doing. Perfect. I added a WHERE clause and an ORDER BY as well, now I have exactly what I needed, and I am impressed with MySQL's capability of doing this. Indeed, thinking in a "set-based" way is much different from everything I've been used to, and worked great. Thank you so much.
    – Pedro A
    Oct 4, 2015 at 3:35

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.