?
Solved

Select query in mysql myISAM tables

Posted on 2012-08-25
4
Medium Priority
?
531 Views
Last Modified: 2012-08-27
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.
0
Comment
Question by:julien80
  • 3
4 Comments
 
LVL 4

Expert Comment

by:NetExpert_pl
ID: 38333598
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.).
0
 

Author Comment

by:julien80
ID: 38334213
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
0
 
LVL 4

Expert Comment

by:NetExpert_pl
ID: 38334623
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

0
 
LVL 4

Accepted Solution

by:
NetExpert_pl earned 2000 total points
ID: 38334638
SELECT 
      categories.category_id,
      category_name, 
      images.image_id, 
      images.image_name, 
      MAX(rating_sum) AS rating_max 
    FROM 
      images 
      LEFT JOIN image_categories 
        ON images.image_id = image_categories.image_id
      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 
      LEFT JOIN categories 
        ON image_categories.category_id = categories.category_id
      GROUP BY category_id
   ;

Open in new window

0

Featured Post

How to Use the Help Bell

Need to boost the visibility of your question for solutions? Use the Experts Exchange Help Bell to confirm priority levels and contact subject-matter experts for question attention.  Check out this how-to article for more information.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Creating and Managing Databases with phpMyAdmin in cPanel.
In this article, I’ll talk about multi-threaded slave statistics printed in MySQL error log file.
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…
Suggested Courses
Course of the Month16 days, 6 hours left to enroll

850 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question