[Dbix-class] Selecting max() of count()

[Iain Hubbard]

On Wed, 2008-07-30 at 14:55 +0200, BUNK (Jacob Bunk Nielsen) wrote:
> $baskets->search({ },
>           { select => [ { count => 'item_id' }, ],
>             as => [ 'no_of_items' ],
>             group_by => [ 'basket_id' ],
>            })->get_column('no_of_items')->max;
> 
> Unfortunately this doesn't cut it. It ends up complaining that there is
> no column called 'no_of_items' for the query:
> 
> SELECT MAX( "no_of_items" )
>  FROM "shop"."basket" "me"
>  WHERE ( ( ( "basket_id" = ? ) OR ( "basket_id" = ? ) ) )
>  GROUP BY "basket_id"" with ParamValues: 1='1', 2='2'
> 
> It totally ignores my part about 'select count(item_id) as no_of_items'.
> 
> How do I make dbix-class make the right database query?

I think its the as => [ 'no_of_items' ] not doing what you think.

"Note that the as attribute has absolutely nothing to with the sql
syntax SELECT foo AS bar"

http://search.cpan.org/~ash/DBIx-Class-0.08010/lib/DBIx/Class/Manual/Cookbook.pod#Using_database_functions_or_stored_procedures

Iain. 
--------------------------------------------------------
GMG Regional Digital is part of the Guardian Media Group plc.




CONFIDENTIALITY NOTICE. The information contained in this e-mail is intended only for dbix-class at lists.scsys.co.uk. It may contain privileged and confidential information that is exempt from disclosure by law and if you are not an intended recipient, you must not copy, distribute or take any action in reliance on it. If you have received this e-mail in error, you may notify us by telephone on 44 (0)161 832 7200. E-mail transmission cannot be guaranteed to be secure or error-free. The sender (iain.hubbard at gmgrd.co.uk) therefore does not accept liability for any errors or omissions in the contents of this message, which arise as a result of e-mail transmission. If verification is required please request a hard-copy version.

Scanned by MailDefender - managed email security from intY - www.maildefender.net