Mark Blythe (l...@markblythe.com)

While I don't use the DBIx::Class::Loader, I did notice that since the
Catalyst::Plugin::Auth refactor, that set of modules doesn't like dealing
with role_id's any longer.  I ended up creating a database view (and
corresponding DBIC class) that spits out role names rather than role IDs,
and Catalyst::Plugin::Authentication::Store::DBIx::Class liked that much
better.

This may not be the only way to solve it, but it worked for me.

Are you sure about this one?  I just tested this with DBI_TRACE, and it does
appear to use bind variables when generating the INSERT statement.  I tried
tripping it up with SQL injections, tossing in quotes, semicolons, etc, and
it always handled it gracefully, as it should when properly using binds.

Hello!

I'd like to announce a relaunch of my web site, Mister Poll, which is now
powered by Catalyst / DBIC:

http://www.misterpoll.com/

I've actually been running the site for 10 years now.  It started out as CGI
(of course) and has been mod_perl for many years now.  When I finally
decided to rewrite the blessed thing from the ground up, I chose Catalyst.
I'm quite glad I did.  For those who may be interested, it is now running on
Linux, MySQL 5.0, Catalyst, DBIC, lighttpd + FastCGI.

Many thanks to all of you on this list (and the DBIC list), who have either
helped me directly or indirectly through the archives.  My gratitude to
everybody who has contributed to produce this software as well.  It's a
great benefit to the Perl community.

Mark

Michael,

See the 'columns' attribute or the 'select / as' attribute combo.  For
example:

$schema->resultset('Foo')->search(
  {  id =3D> { -in =3D> [ 1, 2, 3 ] }  },
  {  columns =3D> [ 'id', 'title' ] }
);

It's covered in more detail in the ATTRIBUTES section of the
DBIx::Class::ResultSet perldocs.  There are also some examples in
DBIx::Class::Manual::Cookbook under 'Using specific columns'.

Good luck,
Mark

Sorry, my mistake.  I missed one change in the example.  You should
use 'select' instead of 'columns'.  At least I did:

$c->stash->{routes} = $c->model('QDB::Routes')->search(
                  {
                          'me.net_id'    => $c->stash->{net_id},
                          'me.route_beg' => $c->stash->{node_id_beg},
                          'me.route_end' => $c->stash->{node_id_end},
                  },
                  {
                          join => { 'xc_params' => 'nodes' },

                          select  => [qw/
                          me.route_id me.route_beg me.route_end
me.err_code me.comment me.net_id
                          xc_params.node_rank xc_params.ch_beg xc_params.ch_end
                          nodes.node_id nodes.node_name
                          / ],

                          as  => [qw/
                          route_id route_beg route_end err_code comment net_id
                          xc_params.node_rank xc_params.ch_beg xc_params.ch_end
                          xc_params.nodes.node_id xc_params.nodes.node_name
                          / ],

                          #TODO Paged output
                          order_by => [ @order_by ],
                          page     => $page,       # page to return
(defaults to 1)
rows => $rows, # number of results per page,
                          cache => 1
                  }
          );

I ran into this just the other day, too.  It has specifically to do
with the nested relationship + fetching a subsest of columns.  You can
do it, but you have to use a "join" instead of a "prefetch" and then
clarify things a bit further with an "as".  Try this:

$c->stash->{routes} = $c->model('QDB::Routes')->search(
                 {
                         'me.net_id'    => $c->stash->{net_id},
                         'me.route_beg' => $c->stash->{node_id_beg},
                         'me.route_end' => $c->stash->{node_id_end},
                 },
                 {
                         join => { 'xc_params' => 'nodes' },

                         columns  => [qw/
                         me.route_id me.route_beg me.route_end
me.err_code me.comment me.net_id
                         xc_params.node_rank xc_params.ch_beg xc_params.ch_end
                         nodes.node_id nodes.node_name
                         / ],

                         as  => [qw/
                         route_id route_beg route_end err_code comment net_id
                         xc_params.node_rank xc_params.ch_beg xc_params.ch_end
                         xc_params.nodes.node_id xc_params.nodes.node_name
                         / ],

                         #TODO Paged output
                         order_by => [ @order_by ],
                         page     => $page,       # page to return
(defaults to 1)
rows => $rows, # number of results per page,
                         cache => 1
                 }
         );

Note that you have to reflect the nested relationship in the naming of
the "as" attribute.

Try that and see if it works for you.

Thanks for clearing that up.  I've written a POD patch (attached).

I haven't submitted one of these before, so I wasn't sure exactly what
version of the file to prepare it against.  This one was prepped for
http://dev.catalyst.perl.org/repos/bast/DBIx-Class/0.08/trunk/lib/DBIx/Clas=
s/Manual/Cookbook.pod,
which is currently at revision 3872.

Let me know if I need to re-do it for any reason.

On Nov 21, 2007 9:24 AM, Matt S Trout <dbix-class@trout.me.uk> wrote:
> > So is this a bug or a feature? BTW, I'm using v.08008
>
> Feature. Both 'a' and 'b' could have a 'c' rel. This is how DBIC
> disambiguates.
>
> It's not documented because I don't think the people writing the docs did
> this sort of selective prefetch; fancy whipping up a POD patch for us? :)
>
> --
> Matt S Trout Catalyst and DBIx::Class consulting and support -
> Technical Director http://www.shadowcat.co.uk/catalyst/
> Shadowcat Systems Ltd. Christmas fun in collectable card game form -
> http://www.shadowcat.co.uk/resources/2007_trad=
ing/
>
> _______________________________________________
> 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.rawmod=
e.org

Attachment: multi-join-select-cookbook.patch

Thanks for clearing that up.  I've written a POD patch (attached).

I haven't submitted one of these before, so I wasn't sure exactly what
version of the file to prepare it against.  This one was prepped for
http://dev.catalyst.perl.org/repos/bast/DBIx-Class/0.08/trunk/lib/DBIx/Class/Manual/Cookbook.pod,
which is currently at revision 3872.

Let me know if I need to re-do it for any reason.

On Nov 21, 2007 9:24 AM, Matt S Trout <dbix-class@trout.me.uk> wrote:
> > So is this a bug or a feature? BTW, I'm using v.08008
>
> Feature. Both 'a' and 'b' could have a 'c' rel. This is how DBIC
> disambiguates.
>
> It's not documented because I don't think the people writing the docs did
> this sort of selective prefetch; fancy whipping up a POD patch for us? :)
>
> --
> Matt S Trout Catalyst and DBIx::Class consulting and support -
> Technical Director http://www.shadowcat.co.uk/catalyst/
> Shadowcat Systems Ltd. Christmas fun in collectable card game form -
> http://www.shadowcat.co.uk/resources/2007_trading/
>
> _______________________________________________
> 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.rawmode.org _______________________________________________
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.rawmode.org

Attachment: multi-join-select-cookbook.patch

In trying what seemed like a simple operation today, I've either
discovered a bug or documentation deficiency.  For the sake of
brevity, I won't show full class definitions here, but let's say I
have 3 simple table classes: A, B, C.  They each have a single primary
key (a_id, b_id, c_id) with straightforward "belongs to" relationships
between them:

A = (belongs to) => B = (belongs to) => C

A simple nested prefetch works fine:

$schema->resultset('A')->find($a_id, {
   prefetch => { b => 'c' }
});

However, if either B or C has a big blob or text column that I don't
want to load, I may want to specify a subset of the columns.  Thus, I
tried the following:

$schema->resultset('A')->find($a_id, {
columns => [ 'me.a_id', 'me.b_id', 'b.b_id', 'b.c_id', 'c.c_id', 'c.title' ],
   join => { b => 'c' }
});

I was careful to specify the keys on both sides of the relationships
just to be safe.  This query does *not* work.  It dies with the error:

No such relationship "c"

I tried using 'select' rather than 'columns', but the same error
occurred.  After much debugging, I discovered that the resultset's
$self->{_attrs}{as} was being built differently than with the
prefetch.  It showed the 'b' and 'c' relationships on the same level
rather than nested, so later on when it died, it was looking for a "c"
relationship in the A class, which of course there isn't.

I was able to fix the query by adding the "as" attribute and nesting
the table prefixes there:

$schema->resultset('A')->find($a_id, {
select => [ 'me.a_id', 'me.b_id', 'b.b_id', 'b.c_id', 'c.c_id', 'c.title' ],
   as => [ 'a_id', 'b_id', 'b.b_id', 'b.c_id', 'b.c.c_id', 'b.c.title' ],
   join => { b => 'c' }
});

If this is expected and necessary, I didn't come across it in the
attribute docs under DBIx::Class::Resultset or in
DBIx::Class::Manual::Cookbook.

So is this a bug or a feature?  BTW, I'm using v.08008

Thanks,
Mark

In trying what seemed like a simple operation today, I've either
discovered a bug or documentation deficiency.  For the sake of
brevity, I won't show full class definitions here, but let's say I
have 3 simple table classes: A, B, C.  They each have a single primary
key (a_id, b_id, c_id) with straightforward "belongs to" relationships
between them:

A = (belongs to) => B = (belongs to) => C

A simple nested prefetch works fine:

$schema->resultset('A')->find($a_id, {
   prefetch => { b => 'c' }
});

However, if either B or C has a big blob or text column that I don't
want to load, I may want to specify a subset of the columns.  Thus, I
tried the following:

$schema->resultset('A')->find($a_id, {
columns => [ 'me.a_id', 'me.b_id', 'b.b_id', 'b.c_id', 'c.c_id', 'c.title' ],
   join => { b => 'c' }
});

I was careful to specify the keys on both sides of the relationships
just to be safe.  This query does *not* work.  It dies with the error:

No such relationship "c"

I tried using 'select' rather than 'columns', but the same error
occurred.  After much debugging, I discovered that the resultset's
$self->{_attrs}{as} was being built differently than with the
prefetch.  It showed the 'b' and 'c' relationships on the same level
rather than nested, so later on when it died, it was looking for a "c"
relationship in the A class, which of course there isn't.

I was able to fix the query by adding the "as" attribute and nesting
the table prefixes there:

$schema->resultset('A')->find($a_id, {
select => [ 'me.a_id', 'me.b_id', 'b.b_id', 'b.c_id', 'c.c_id', 'c.title' ],
   as => [ 'a_id', 'b_id', 'b.b_id', 'b.c_id', 'b.c.c_id', 'b.c.title' ],
   join => { b => 'c' }
});

If this is expected and necessary, I didn't come across it in the
attribute docs under DBIx::Class::Resultset or in
DBIx::Class::Manual::Cookbook.

So is this a bug or a feature?  BTW, I'm using v.08008

Thanks,
Mark

If the controller truly never sees DBIC stuff, does that mean that
your model logic never returns DBIC objects?  For instance, let's say
you have a logic method called findBestFit() that's supposed to return
shoes that fit a given person and activity the best.  Would it return
a DBIC ResultSet made up of Shoe row objects, or would findBestFit()
deal with those objects only internally and construct something
non-DBIC for the return?

That's a choice I struggled with up front, and I finally decided that
sticking with DBIC objects made better sense for me than inventing a
whole new layer simply to abstract them and provide similar but not
DBIC-specific interfaces.

Agreed.  It would definitely be helpful if it'd throw an error in that case.