[Dbix-class] help to retrieve data efficiently

[James. L]

sorry for replying myself. after reread the doc few more times (thx for the  Multi-step prefetch doc) and played with the dbic more, i came up with the solution, i think:

besides the belongs_to relationships, i added two more relationships in MyApp::Schema::Result::UserPost.pm

__PACKAGE__->has_many('tags','MyApp::Schema::Result::UserTagPost','post_id');
__PACKAGE__->has_one('category','MyApp::Schema::Result::UserCategoryPost','category_id');

perl -MMyApp -le '
$r = MyApp->model("UserPost")
          ->search( { "me.user_id" => 1 }, 
                    { prefetch => { "post" => { "tags" => "tag" } },
                      order_by => [qw/ post.created / ]
                    });
@x = $r->first->post->tags; print $x[1]->tag->tag_text'


cheers,

Qiang


--- On Wed, 7/9/08, James. L <perlwle at yahoo.com> wrote:

> From: James. L <perlwle at yahoo.com>
> Subject: [Dbix-class] help to retrieve data efficiently
> To: dbix-class at lists.scsys.co.uk
> Date: Wednesday, July 9, 2008, 5:28 AM
> 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
> 
> 
>       
> 
> _______________________________________________
> List:
> http://lists.scsys.co.uk/cgi-bin/mailman/listinfo/dbix-class
> IRC: irc.perl.org#dbix-class
> SVN: http://dev.catalyst.perl.org/repos/bast/DBIx-Class/
> Searchable Archive:
> http://www.grokbase.com/group/dbix-class@lists.rawmode.org