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

[BUNK (Jacob Bunk Nielsen)]

Hi

I'm using PostgreSQL 8.2 and DBIx-Class 0.08007.

Let's say I have a shop table with a basket_id (identifying a shopping
basket) and an item_id (identifying an item in a basket). Now I want to
find out how many items there are in the basket with the most items.

I'm trying to write the equivalent of:

select
max(no_of_items) from
(select count(item_id) as no_of_items
 from shop.basket where basket_id=1 or basket_id=2
 group by basket_id) foo;

So I have done something like:

my $baskets = $schema->resultset('shop.basket')
    ->search({basket_id => [1, 2]});

$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?

Best regards

Jacob