SitePoint Sponsor |
|
User Tag List
Results 1 to 6 of 6
-
Oct 27, 2009, 14:47 #1
- Join Date
- May 2006
- Posts
- 3
- Mentioned
- 0 Post(s)
- Tagged
- 0 Thread(s)
Many-to-many - restrict to one w/ GROUP BY, but fetch all
Hi all. Hope that subject wasn't too confusing ... here's what I'm going for:
I have a table of videos, and a table of themes. Each video is associated with 3 themes through the videos_themes table (thus it's a many-to-many relationship).
There is a page where you can browse videos by selecting a theme. So on this page I want to show only those videos which have the selected theme as one of its 3 themes, but at the same time, I want to fetch the 3 themes for each video matched, so they can be displayed on the page. Rough example:
Code:Select theme: <baseball> * Video 1 - themes: baseball, rain, bloopers * Video 2 - themes: friends, picnics, baseball * Video 3 - etc.
Code MySQL:SELECT v.*, GROUP_CONCAT(t.name) AS themes_list FROM videos v INNER JOIN videos_themes vt1 ON v.id = vt1.video_id INNER JOIN videos_themes vt2 ON v.id = vt2.video_id INNER JOIN themes t ON vt2.theme_id = t.id WHERE vt1.theme_id = <selected theme id> # reduces vt1 to just one row per video GROUP BY v.id # one row per video
I'm just feeling like this isn't very graceful and there must be a better/faster way to do it. The query (which is actually a good deal more complex than the above) is taking 3-4 ms, which doesn't seem too bad--although the table is still pretty small. But I'm not sure this is the best way--I'd be grateful for any criticism.
-
Oct 27, 2009, 15:32 #2
- Join Date
- Jul 2002
- Location
- Toronto, Canada
- Posts
- 39,347
- Mentioned
- 63 Post(s)
- Tagged
- 3 Thread(s)
looks pretty good to me
it gets the right answer, doesn't it?
as for performance, did you declare any indexes on those tables?
-
Oct 27, 2009, 18:53 #3
- Join Date
- May 2006
- Posts
- 3
- Mentioned
- 0 Post(s)
- Tagged
- 0 Thread(s)
Hey r937, thanks for the reply.
Yes it does get the right answer. I have used two joins to the same table before, but it just seems like a last-ditch kind of thing to do
As for indexes, yes, the 'id' columns are all primary keys (obviously), and the videos_themes table just consists of those two columns -- video_id and theme_id -- and the primary key is on both of them (compound). Come to think of it, I should probably index them individually too for the purposes of the WHERE and (JOIN) ON clauses in this query. Or maybe it doesn't matter since there are no other columns anyway?
-
Oct 27, 2009, 19:10 #4
- Join Date
- Jul 2002
- Location
- Toronto, Canada
- Posts
- 39,347
- Mentioned
- 63 Post(s)
- Tagged
- 3 Thread(s)
since your PK is (video_id,theme_id) -- which is correct, by the way, thanks for not tossing an auto_increment in there -- then all you need is an additional index on (theme_id,video_id)
-
Oct 27, 2009, 19:14 #5
- Join Date
- May 2006
- Posts
- 3
- Mentioned
- 0 Post(s)
- Tagged
- 0 Thread(s)
Yeah auto columns on xref tables irk me
So you're saying I should have one index with video_id first and another with theme_id first? That makes sense to me. What if the second index was just (theme_id)? What difference would this make?
-
Oct 27, 2009, 19:30 #6
- Join Date
- Jul 2002
- Location
- Toronto, Canada
- Posts
- 39,347
- Mentioned
- 63 Post(s)
- Tagged
- 3 Thread(s)
Bookmarks