asked on

Select query in mysql myISAM tables

Hello,

I am having trouble selecting best rated images in a gallery that is categorized. The project wants to be an image library with categorized images.

What i would like to do is to design the tables, and the selection query, in such way that i will be able to execute one query and obtain Best performing image from every category, something like the image that is best rated in its category. All this within one query.
The result should show the list of my predefined categories with its corresponding image that is best rated in its category at the moment.

I know i can do this with for loops in php and issue a query for every category, but that will not be a optimized way.

The table design is still a work in progress but if someone can point me to the basic idea of the select format that would work on my test tables would be rewarded.

PS : I have tried a lot of select variations combined with GROUP BY but still no success.

Also in my design i use a table for categories and one for the actual thumbnails.

NetExpert_pl

Tables:

+-------------+---------------+
| category_id | category_name |
+-------------+---------------+
|           1 | cat1          |
|           2 | cat2          |
|           3 | cat3          |
|           4 | cat4          |
+-------------+---------------+

+----------+------------+-------------+
| image_id | image_name | category_id |
+----------+------------+-------------+
|        1 | img1       |           1 |
|        2 | img2       |           1 |
|        3 | img3       |           2 |
|        4 | img4       |           2 |
|        5 | img5       |           2 |
|        6 | img6       |           3 |
+----------+------------+-------------+

+-----------+----------+--------+
| rating_id | image_id | rating |
+-----------+----------+--------+
|         1 |        1 |      3 |
|         2 |        1 |      5 |
|         3 |        2 |      5 |
|         4 |        3 |      4 |
|         5 |        4 |      1 |
+-----------+----------+--------+

Query:

SELECT 
  categories.category_id, 
  category_name, 
  image_id, 
  image_name, 
  rating_max 
FROM 
  categories 
  LEFT JOIN (
    SELECT 
      category_id, 
      images.image_id, 
      images.image_name, 
      MAX(rating_sum) AS rating_max 
    FROM 
      images 
      LEFT JOIN (
        SELECT 
          image_id, 
          SUM(rating) AS rating_sum 
        FROM 
          ratings 
        GROUP BY image_id
      ) rating_sums
      ON 
        images.image_id = rating_sums.image_id 
      GROUP BY category_id
    ) rating_maxes 
  ON 
    categories.category_id = rating_maxes.category_id;

+-------------+---------------+----------+------------+------------+
| category_id | category_name | image_id | image_name | rating_max |
+-------------+---------------+----------+------------+------------+
|           1 | cat1          |        1 | img1       | 8          |
|           2 | cat2          |        3 | img3       | 4          |
|           3 | cat3          |        6 | img6       | NULL       |
|           4 | cat4          | NULL     | NULL       | NULL       |
+-------------+---------------+----------+------------+------------+

Open in new window

Do you need to add images to more than one category?

You will want to add more fields to the tables (like image data or path etc.).

julien80

ASKER

Hello and thank you for your fast reply.

There are going to be other fields in the tables but this example illustrates what i mainly wanted.

Also the images will be rather "tagged" using keywords, those i called previously categories. So, there will be images that will have multiple tags(categories). Therefore, yes i will need to add images to more than one category.

Also in the table design i am having trouble with the layout on how to do multiple categories("tags") for every image. Should i add let's say 5 columns with the eventual category ids(this will make a maximum 5 categories for every image, not so good) or i should use some sort of a VARCHAR column and store the categories ("tags") as comma separated strings and then use FULLTEXT search when comes to the query above. This would significantly make the query more complex and speed is an issue.

Its worth mentioning that the image index will keep growing constantly and temporary tables are not desired.

Thank you and waiting for your valuable input

NetExpert_pl

mysql> select * from images;
+----------+------------+
| image_id | image_name |
+----------+------------+
|        1 | img1       |
|        2 | img2       |
|        3 | img3       |
|        4 | img4       |
|        5 | img5       |
|        6 | img6       |
+----------+------------+
6 rows in set

mysql> select * from image_categories;
+----------+-------------+
| image_id | category_id |
+----------+-------------+
|        1 |           1 |
|        1 |           2 |
|        2 |           1 |
|        3 |           2 |
|        4 |           2 |
|        5 |           2 |
|        6 |           3 |
+----------+-------------+
7 rows in set

Open in new window

ASKER CERTIFIED SOLUTION

NetExpert_pl

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial