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

[linuxsupport]

Thanks for the reply.

I have something like

my $rs =3D $schema->resultset('Article')->search(
{
   "me.id" =3D> 1
},
{
    join =3D> [
               {arti_author =3D> "author"},
               {arti_comnt =3D> "comments"},
              ],
     select =3D> [
                 qw/ me.create_date author.author_name comments.comment
comments.comment_date comments.name /
                  ],
       as =3D> [
                  qw/ created author comment comment_date commented_by /
                 ]
}
);

How can I use prefetch to simplify it?

Thanks

On Fri, Dec 31, 2010 at 2:01 PM, Wolfgang Kinkeldei
<wolfgang at kinkeldei.de>wrote:

> 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 y=
our
> 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 t=
he
> 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 =3D> id
> > ArticleAuthor (article_id, author_id) primary key =3D> (article_id,
> author_id)
> > Author (id, author_name) primary key =3D> id
> >
> > Relationship.
> >
> > Article.pm
> > has_many(article_author =3D> "Article::Author", "article_id")
> >
> > ArticleAuthor.pm
> > has_many(author =3D> "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 =3D> '1'
> > },
> > { join =3D> { article_author =3D> author }
> >   prefetch =3D> { article_author =3D> author }
> > }
> > );
> >
> > This returns me all the table filelds from all three table, I tried to
> put something columns =3D> [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??,"".(($/=3Dq|Cms)+-03467:;<=3D|)=3D~tr!C-z -B! -z=
!)x
> '( o.o )' .$/]->[hex]foreach split qr<>,qq+1ecd039ad65b025b8063475b+||
> ' > ^ < ' .q<!-- Wolfgang Kinkeldei - mailto:wolfgang at kinkeldei.de -->
>
>
>
> _______________________________________________
> 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
>
-------------- next part --------------
An HTML attachment was scrubbed...
URL: http://lists.scsys.co.uk/pipermail/dbix-class/attachments/20101231/e67=
a0755/attachment.htm