[Dbix-class] More funky sql

[Matt S Trout]

On Mon, Jan 16, 2006 at 04:11:49PM -0600, Brandon Black wrote:
> Having been foiled earlier, I'm now looking through the rest of my
> code for cases the branch might not handle well, or perhaps that I
> just haven't discovered how to make the branch handle well, whatever
> the case.  This is the first one I've come across so far:
> 
> DISTINCT/COUNT stuff
> 
> attrs { distinct => 1 } seems to do "GROUP BY" rather than DISTINCT. 
> I think that works, and reasonably emulates what DISTINCT would have
> done in normal use, but I can't be sure for all cases, I don't know. 
> The reasoning behind this is lost on me, but I'm sure there's a
> reason. 

Largely the fact that some databases (notably SQLite) can't handle DISTINCT
with more than one argument :(

There is, however, absolutely nothing to stop you doing

$source->resultset->search(...,
  { select => [ { distinct => [ $source->columns ] } ],
    as => [ $source->columns ] });

> Of course "GROUP BY" doesn't work with count, so { distinct
> => 1 } doesn't either.  Is there a construct that exists or that we
> can make to handle the case:
> 
> SELECT COUNT(DISTINCT colname) FROM table [ WHERE ... ]
> 
> Perhaps:
> 
> ->count( {}, { cols => [ 'colname' ], distinct => 1 } )

->search(..., { select => [ { count => { distinct => 'colname' } } ],
                as => [ 'count' ] })->cursor->next;

though that'll generate COUNT(DISTINCT(colname)) currently, which may or
may not be right (I'm being mondayed, sorry)

> Where if @$cols == 1 && attrs{distinct}, issue the statement like the
> SQL above, instead of bombing because distinct (as emulated by
> group_by) and count cannot go together?

There should probably be a hack to 'count' that special cases this. Or maybe
just makes the DISTINCT call anyway, and if it dies, well, blame your db.

-- 
     Matt S Trout       Offering custom development, consultancy and support
  Technical Director    contracts for Catalyst, DBIx::Class and BAST. Contact
Shadowcat Systems Ltd.  mst (at) shadowcatsystems.co.uk for more information

 + Help us build a better perl ORM: http://dbix-class.shadowcatsystems.co.uk/ +