Page 1 of 1

Efficient query for gallery front page

Posted: Wed Feb 28, 2007 9:07 am
by jolinar
feyd | Please use

Code: Select all

,

Code: Select all

and [syntax="..."] tags where appropriate when posting code. Your post has been edited to reflect how we'd like it posted. Please read:  [url=http://forums.devnetwork.net/viewtopic.php?t=21171]Posting Code in the Forums[/url] to learn how to do it too.[/color]


Sorry the title isn't very descriptive, but it's a strange problem I'm trying to solve which may take a while to explain.

I'm trying to build a gallery, and on the front page I need to build a list of categories and albums.  The idea is that each album will have it's name, description and a sample image (the most recent added)

These albums are then grouped by category.  The problem is that I need to get data from 3 different tables, and ideally I'd like to do it all in the same query.

This is the query I used for getting album data and grouping it by cat:
[syntax="sql"]SELECT * FROM gallery_category INNER JOIN gallery_album ON(category_id=album_category) ORDER BY category_id;
Then I decided that I needed preview images, this is where I became very confused. The only way I know to link this to the photo urls is through an inner join, but this produces a large amount of redundant data.

Code: Select all

SELECT * FROM (gallery_category INNER JOIN gallery_album ON(category_id=album_category)) INNER JOIN gallery_photo USING(album_id) ORDER BY category_id;
With this being the worst query imaginable (Since it produces rows for each photo, with all the album/category information associated with it)

Does anyone have any ideas on how to solve this?


feyd | Please use[/syntax]

Code: Select all

,

Code: Select all

and [syntax="..."] tags where appropriate when posting code. Your post has been edited to reflect how we'd like it posted. Please read:  [url=http://forums.devnetwork.net/viewtopic.php?t=21171]Posting Code in the Forums[/url] to learn how to do it too.[/color]

Posted: Wed Feb 28, 2007 9:09 am
by Begby
Does the gallery_photo table have a date field that indicates when the photo was added?

Posted: Wed Feb 28, 2007 9:14 am
by feyd
Have you tried a GROUP BY clause for "album_id"? You will probably need to add another clause to the ORDER BY as well, either using the date field (if one exists) or potentially the id numbers of the photos.

Posted: Wed Feb 28, 2007 9:30 am
by jolinar
Thanks feyd, the GROUP BY clause fixed it :D (I guess I'm still a numpty when it comes to SQL)