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 <[email protected]> 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/[email protected]
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:[email protected] -->
_______________________________________________
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/[email protected]
