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

[Randy Moore]

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 = $c->model('PjAppDB::Categories')->search(undef, {
>>>                    select => [
>>>                      'name',
>>>                      { length => 'name' }
>>>
>>>                    ],
>>>                    as => ['name', 'namelength'],
>>>            });
>>>            my $result = $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 = $foo_rs->search({}, {
>>            select => [{ COUNT => 1 }],
>>            as       => '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/FAQ.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 => [ \'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 => [ 'name', \'now() AS currenttime'], as => [ 'name', ] })

Thanks for the help.


>
>  
>
>
>
> hth,
> John
> ------------------------------------------------------------------------
>
> _______________________________________________
> 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


-- 
Randy Moore
Axion Information Technologies, Inc.

phone: 301-587-3300 x 511
fax:   301-585-7450

http://www.axion-it.com