[Dbix-class] More funky sql

[Zbigniew Lukasiak]

I tried Matt's advice on my 'Count in joins' example, and the count
method returns still wrong results, although the private _column_data
hash does have the right count  field.  If I tried "as =>
['some_non_existing_col']"  I was getting an error.

Here are the details.

I tested it against the branch version 505.

My program:

use strict;
use lib '/home/zby/progs/DBIx-Class-resultset/lib';

package Model;
use base 'DBIx::Class';
__PACKAGE__->load_components(qw/PK::Auto::Pg Core DB/);
__PACKAGE__->connection('dbi:Pg:dbname=test', '', '');

package Model::Car;
use base 'Model';
__PACKAGE__->table('car');
__PACKAGE__->add_columns(qw/ id usr color /);
__PACKAGE__->set_primary_key('id');


package Model::Person;
use base 'Model';
__PACKAGE__->table('person');
__PACKAGE__->add_columns(qw/ id nick /);
__PACKAGE__->set_primary_key('id');
__PACKAGE__->has_many(
   car => 'Model::Car', {'foreign.usr' => 'self.id'}
);

my $it = Model::Person->search(
   { 'car.color' => 'red' },
   {
       join => [ 'car' ],
       select => [ { 'count' => 'distinct me.id' } ],
       as => [ 'count' ],
   }
);

use Data::Dumper;
my $result = $it->next();
print Dumper($result);
print 'Count: ' . $result->count() . "\n";

=========================

As before my tables where:

reate table person(
id integer primary key,
nick varchar(100));

create table car(
id integer primary key,
usr integer, color varchar(100),
foreign key(usr) references person(id)
);


insert into person values (1, 'anna');
insert into person values (2, 'jolka');

insert into car values (1, 1, 'white');
insert into car values (2, 2, 'red');
insert into car values (3, 2, 'red');
insert into car values (4, 2, 'red');

==============
The results:
$VAR1 = bless( {
                 'result_source' =>  ....
.
.
.
                 '_in_storage' => 1,
                 '_column_data' => {
                                     'count' => '1'
                                   }
               }, 'Model::Person' );
Count: 2


Zbyszek

On 1/16/06, Matt S Trout <dbix-class at trout.me.uk> wrote:
> 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/ +
>
> _______________________________________________
> List: http://lists.rawmode.org/cgi-bin/mailman/listinfo/dbix-class
> Wiki: http://dbix-class.shadowcatsystems.co.uk/
> IRC: irc.perl.org#dbix-class
> SVN: http://dev.catalyst.perl.org/repos/bast/trunk/DBIx-Class/
>