[Dbix-class] Fwd: [Catalyst] Selecting from more tables (DBIC-bug?)

[Peter Rabbitson]

Octavian Rasnita wrote:
> From: "iain" <iainhubbard at googlemail.com>
> 
>>
>>> my $uuu = $schema->resultset('User')->search({},{
>>> prefetch => {blogs => 'blog_comments'},
>>> select => ['me.id'],
>>> as => ['user_id'],
>>> });
>>>
>>> print $uuu->first->username;
>>>
>>> I think the single column that should be printed should be me.id, but
>>> here is the generated SQL:
>>>
>>> SELECT me.id, blogs.id, blogs.user, blogs.date_create,
>>> blogs.date_modify, blogs.title, blogs.body, blogs.markup_lang,
>>> blogs.tags, blogs.active, blog_comments.id, blog_comments.user,
>>> blog_comments.blog, blog_comments.date_time, blog_comments.body,
>>> blog_comments.markup_lang, blog_comments.active FROM user me LEFT
>>> JOIN blog blogs ON blogs.user = me.id LEFT JOIN blog_comment
>>> blog_comments ON blog_comments.blog = blogs.id ORDER BY blogs.user,
>>> blog_comments.blog:
>>>
>>> Is it normal to get all the columns from the joined tables?
>>>
>>
>> Yes. You have asked DBIC to prefetch the data from the blogs tables.
>> So it has.
>>
>> Iain.
> 
> 
> I know, but I would like to prefetch only just a few columns from the
> joined tables, not all of them.
> 
> Even if I would add to that arrayref some columns from the joined table,
> DBIC will get all the columns from those tables.
> 
> So, it is not possible to select only some specific columns from the
> joined tables?
> 
It is possible. All you do is specify the 'as' attribute properly:

{
  join => { foo => 'bar' },
  select =>  [qw/ me.name foo.title bar.label /],
  as => [qw/ name foo.title foo.bar.label ],
}

Note the multiple dots in 'as' - this is an idnication how to
inflate data into multiple objects.

The downside is that if foo or bar are joins of type-multi, you will
lose the collapse feature. For this you can *only* use prefetch which
will *always* select all columns. The amount of work necessary to work
around this limitation does not justify the results.

If you are willing to spend the time hacking on this - join us on
perl.irc.org#dbix-class

Cheers