Trying again to forward to DBIC list, which rejected my previous try
at forwarding with additions...
From: "Octavian Rasnita" <orasn...@gmail.com>
To: "The elegant MVC web framework" <catalyst@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?
_______________________________________________
List: Catalyst@lists.scsys.co.uk
Listinfo: http://lists.scsys.co.uk/cgi-bin/mailman/listinfo/catalyst
Searchable archive: http://www.mail-archive.com/catalyst@lists.scsys.co.uk/
Dev site: http://dev.catalyst.perl.org/
