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

[Thomas L. Shinnick]

Trying again to forward to DBIC list, which rejected my previous try 
at forwarding with additions...

>From: "Octavian Rasnita" <orasnita at gmail.com>
>To: "The elegant MVC web framework" <catalyst at lists.scsys.co.uk>
>Date: Sun, 17 Jan 2010 15:22:57 +0200
>Subject: [Catalyst] Selecting from more tables (DBIC - bug?)
>
>Hi,
>
>Sorry for not writing to the DBIC mailing list, but it rejects my messages
>as SPAM.
>
>I have tried the following select from the table "user":
>
>return $self->search_rs({},{
>prefetch => {blogs => 'blog_comments'},
>'+select' => ['me.id'],
>'+as' => ['user_id'],
>});
>
>The table user has_many blogs and it also has_many blog_comments.
>
>The table blog has_many blog_comments and belongs_to user.
>
>The table blog_comment belongs_to user and belongs_to blog.
>
>The problem is that the +select and +as options have no effect, and the
>query above returns all the columns from all 3 tables, no matter what
>columns I select.
>
>It seems to work only if I use "join" instead of "prefetch" and "select" and
>"as" instead of "+select" and "+as".
>
>I use ActivePerl 5.10.1 and the latest versions of DBIx::Class and
>SQL::Abstract.
>
>Is there a bug or I am missing something obvious, or it is just not possible
>what I want?
>
>Thank you.
>
>Octavian

I'm having some of the same questions regarding +select and +as .  It
seems that using '+select' and '+as' does not stop other columns from
being returned.  I'm using DBIC 0.08115.

I've got a 3 table test setup, which might be confusing, but please just
look at the SELECT lines below.

First I use the syntax as documented, with the '+' in front of 'select'
and 'as'.

   $rs1 = $schema->resultset('Users')
                 ->search( { 'me.is_admin' => '1' },
             { join      => { 'users_roots' => 'root_id' },
               '+select' => [ 'me.user_id',
                              'me.user_name',
                              'root_id.rootpath',
                            ],
               '+as'     => [ 'admin_id',
                              'admin_name',
                              'admin_rootpath',
                            ],
               'order_by' => ['root_id.rootpath'],

When I query a returned row using get_column() I get something for every
name queried, table column or 'as' specified name.

       #   ( I see '2' for user_id )
       #   ( I see '1admin' for password )
       #   ( I see '2' for admin_id )

And looking at the SELECT line you can see that what is being requested
isn't just what was asked for, and some columns are requested twice!

     SELECT me.user_id, me.user_name, me.password, me.is_admin, me.info,
            me.user_id, me.user_name, root_id.rootpath
       FROM users me
       LEFT JOIN users_roots users_roots ON users_roots.user_id = me.user_id
       LEFT JOIN roots root_id ON root_id.root_id = users_roots.root_id
      WHERE ( me.is_admin = ? ) ORDER BY root_id.rootpath: '1'


Here I just take the '+' out of '+select' and '+as':

               'select' => [ 'me.user_id',
                              'me.user_name',
                              'root_id.rootpath',
                            ],
               'as'     => [ 'admin_id',
                              'admin_name',
                              'admin_rootpath',
                            ],

And now some of the unrequested columns are now gone or renamed as requested:

       #   ( I see '<undef>' for user_id )
       #   ( I see '<undef>' for password )
       #   ( I see '2' for admin_id )

And that is the story that the SELECT line tells also - only the 3 columns
I've asked for are requested:

     SELECT me.user_id, me.user_name, root_id.rootpath
       FROM users me
       LEFT JOIN users_roots users_roots ON users_roots.user_id = me.user_id
       LEFT JOIN roots root_id ON root_id.root_id = users_roots.root_id
      WHERE ( me.is_admin = ? ) ORDER BY root_id.rootpath: '1'

So while I'm not seeing a difference with 'join' and 'prefetch' with
Octavian (and that is probably because my debug tests aren't good enough),
I am seeing a concrete difference about retrieved columns.  What is
supposed to be true?