[Dbix-class] help to retrieve data efficiently

[James. L]

Hi, all

i am having trouble to retrieve data efficiently cross different tables. being less experienced with schema setup and dbic, i am hoping someone can shed some light on my problem..

I have the following tables:

Tag (id, tag)
Category (id, category)
UserPost (user_id, post_id)
UserCategoryPost (user_id, category_id, post_id)
UserTagPost  (user_id, tag_id, post_id)
 
category and tag are optional for a post. 
a post could have only 0 or 1 category. a post could have 0 or more tags.
the only relationships that i have setup are the belongs_to for user_id, tag_id, category_id, post_id.
 
now i am trying to grab posts, say 10 posts by a user, and along with the tags and category if a post has them.  

the way i can think of getting those result for one post is one sql to grab post from 'UserPost' table using prefetch. then two sqls to grab the 'Tag' and 'Category'. for 10 posts that would mean
1 sql for 10 posts + 20 sqls for 'Tag' and 'Category' for 10 posts.


TIA,

Qiang