[Dbix-class] Is the 'as' attribute to a search method broken?

[Jonas Alves]

On 29/10/2007, Randy Moore <ramoore at axion-it.com> wrote:
>
> John Goulah wrote:
> >
> >
> > On 10/28/07, *Randy Moore* <ramoore at axion-it.com
> > <mailto:ramoore at axion-it.com>> wrote:
> >
> >     Jonathan Rockway wrote:
> >>     Randy Moore wrote:
> >>
> >>>     Hi,
> >>>
> >>>     I'm very new with DBIx-Class, but I don't think I'm doing anything
> >>>     wrong <grin>.  It looks like the 'as' attribute to a search is not
> >>>     working.
> >>>     I'm running inside of Catalyst BTW, and I've created a trivial
> example
> >>>
> >>>     that I think should work.
> >>>
> >>>            my $rs =3D $c->model('PjAppDB::Categories')->search(undef,=
 {
> >>>                    select =3D> [
> >>>                      'name',
> >>>                      { length =3D> 'name' }
> >>>
> >>>                    ],
> >>>                    as =3D> ['name', 'namelength'],
> >>>            });
> >>>            my $result =3D $rs->first();
> >>>
> >>>     My Categories model class *does* have a column named 'name'.
> >>>
> >>>
> >>>     This code is producing:   SELECT name, LENGTH( name ) FROM
> categories me
> >>>     But I think it should produce:  SELECT name name, LENGTH( name )
> >>>     namelength FROM categories me
> >>>
> >>>     The SQL query that it does produce works perfectly, I just don't
> see
> >>>
> >>>     how I can access the result for the LENGTH.
> >>>     I was expecting to use $result->get_column('namelength')
> >>>
> >>>     Am I missing something blindingly obvious?
> >>>
> >>     I'm not really sure what you're trying to do... but keep in mind
> that
> >>     "as" has nothing to do with the SQL produced.  The "as" name is
> what you
> >>     pass to "get_column" when you want that data.  An example:
> >>
> >>
> >>        my $result =3D $foo_rs->search({}, {
> >>            select =3D> [{ COUNT =3D> 1 }],
> >>            as       =3D> 'foos',
> >>        });
> >>
> >>     This will produce something equivalent to "SELECT *, COUNT(1) FROM
> >>
> >>     ....".  The information that the database returns as COUNT(1) will
> be
> >>     available as "$result->get_column('foos')".
> >>
> >>     Regards,
> >>     Jonathan Rockway
> >>
> >     Ok, I now see that the 'AS' info does not actually get built into
> >     the SQL query.  So, what I'm trying to do will not work.
> >
> >
> >
> > Also, from the FAQ
> >
> http://search.cpan.org/~ash/DBIx-Class-0.08007/lib/DBIx/Class/Manual/FAQ.=
pod
> > <
> http://search.cpan.org/%7Eash/DBIx-Class-0.08007/lib/DBIx/Class/Manual/FA=
Q.pod
> >
> >
> > .. sort my results based on fields I've aliased using |as|?
> >
> >     You don't. You'll need to supply the same functions/expressions to
> >     |order_by|, as you did to |select|.
> >
> >     To get "fieldname AS alias" in your SQL, you'll need to supply a
> >     literal chunk of SQL in your |select| attribute, such as:
> >
> >      ->search({}, { select =3D> [ \'now() AS currenttime'] })
> >
> >     Then you can use the alias in your |order_by| attribute.
> >
> That was the key to my problem.  I had read the FAQ a couple of times
> while learning about other features, but didn't remember this bit.
> There is a lot to absorb with a package as powerful and flexible as DBIC.
>
> But, to get my query fully working, I had to to a slight variation on
> the FAQ solution.   I was pulling other specific columns in addition to
> the function, so I had to use the equivalent of:
>
> ->search({}, { select =3D> [ 'name', \'now() AS currenttime'], as =3D> [
> 'name', ] })
>
> Thanks for the help.
>
>

Try to use '+as' instead of 'as'. See the pod from
DBIx::Class::Manual::Joining and DBIx::Class::ResultSet and search for
'+as'.

-- =

Jonas
-------------- next part --------------
An HTML attachment was scrubbed...
URL: http://lists.scsys.co.uk/pipermail/dbix-class/attachments/20071029/628=
c4768/attachment-0001.htm