On Wed, Jul 30, 2008 at 1:55 PM, BUNK (Jacob Bunk Nielsen)
<[EMAIL PROTECTED]> wrote:
> 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?
>
Could be a ResultSetColumn bug. What's the SQL you are actually expecting?
_______________________________________________
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]
