[Dbix-class] Re: Best possible way to retrieve data using relationship

[Wolfgang Kinkeldei]

Hello <linuxsupport>,

looks like nobody answered before...

You will find an answer to your questions if you carefully read the documentation in
- DBIx::Class::Manual::Joining
- DBIx::Class::Manual::Cookbook

To give a short answer, you should never use "join" and "prefetch" in the same query, these options are mutually exclusive.

If you choose to use "join" your result will have as many rows as your SQL statement retrieves. You will need to add extra "select" or "+select" options paired with "as" or "+as" entries to get the column values from your related tables. Please keep in mind that no extra accessors for "as" / "+as" generated columns will be generated. You must fetch these values via "$result->get_column('name_of_the_column')".

On the other hand, if you use "prefetch", your resultset will only have the number of rows, that are distinct in the main table of your query. Then, you can access all prefetched tables using their accessor methods. In contrast to a joined DBIx-Query this will not trigger additional SQL fired to your database, as the joined tables' data has already been read. Prefetch by convention always reads every column in all tables used in the query.


Am 30.12.2010 um 05:05 schrieb linuxsupport:

> Hi,
> 
> anyone can direct me on this please?
> 
> On Tue, Dec 28, 2010 at 4:32 PM, linuxsupport <lin.support at gmail.com> wrote:
> Hi,
> 
> I am trying to find best way to fetch data from multiple table using DBIx::Class relationship.
> 
> I have 3 tables as follows.
> 
> Articles ( id, title, sort_text) primary key => id
> ArticleAuthor (article_id, author_id) primary key => (article_id, author_id)
> Author (id, author_name) primary key => id
> 
> Relationship.
> 
> Article.pm
> has_many(article_author => "Article::Author", "article_id")
> 
> ArticleAuthor.pm
> has_many(author => "Author", "id")
> 
> I want to get title and sort_text from articles table and author_name form author table for a given id.
> I tried this
> $schema->resultset('Article)->search(
> {
>    id => '1'
> },
> { join => { article_author => author }
>   prefetch => { article_author => author }
> }
> );
> 
> This returns me all the table filelds from all three table, I tried to put something columns => [qw/ title sort_text author_name/] but did not work.
> 
> I want to know if above is correct way to use relationship? or is there a better way of doing the same?
> And how I can get only required columns.
> 
> Thanks
> 
> _______________________________________________
> 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.scsys.co.uk


Best,

Wolfgang Kinkeldei

-- 

' /\_/\ ' .print[split??,"".(($/=q|Cms)+-03467:;<=|)=~tr!C-z -B! -z!)x
'( o.o )' .$/]->[hex]foreach split qr<>,qq+1ecd039ad65b025b8063475b+||
' > ^ < ' .q<!-- Wolfgang Kinkeldei - mailto:wolfgang at kinkeldei.de -->