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

[John Goulah]

On 10/28/07, Randy Moore <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.
>
> I'm really trying to use the result of a SQL function in the 'order_by'
> clause.
>
> The SQL I'm trying to produce is something like:
>
> SELECT name, LENGTH( name ) AS namelength ORDER BY namelength
>
> This is one small piece of a search query I'm dynamically building up
> based on results from an html form.  I've got it all working but the
> ordering.  And, I'm trying to avoid re-ordering the resulting data in code
> because I'm using the page and rows attributes to page through the result=
s.
>
> I had thought the 'AS' attribute was the key, so I"m off to find another
> approach.
>


Have you read:

http://search.cpan.org/~ash/DBIx-Class-0.08007/lib/DBIx/Class/Manual/Cookbo=
ok.pod#Using_database_functions_or_stored_procedures
http://search.cpan.org/~ash/DBIx-Class-0.08007/lib/DBIx/Class/Manual/Cookbo=
ok.pod#Grouping_results
-------------- next part --------------
An HTML attachment was scrubbed...
URL: http://lists.scsys.co.uk/pipermail/dbix-class/attachments/20071029/212=
fad54/attachment.htm