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 _______________________________________________ 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/[EMAIL PROTECTED]