[Dbix-class] Search in joins

[Zbigniew Lukasiak]

Hi,

This letter is a kind of simple Perlmonks style meditation over
searching and counting in joins.  It is based on the not yet released
DBIx-Class-resultset branch of the library.

In my project I had a one to many join and I needed to search on the
'one' side but using some properties from the 'many' side and I found
that quite difficult (if that was a bit ambigues don't worry - example
below will make it clear).  I hope this meditation will help others in
the same situtation.

Let's assume we have a following database schema:

create 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)
);

As promised - a simple one - to many relation.  Let's insert some values there:

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');


Here are the DBIx::Class table definitions that I thought up:

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'}
);

I've left out the link from the car table to the person table so that
we can have both classes in the same file and not have some
compilation problems. It is not relevant for our meditation - it is
for searches like:

 'find all cars that belong to anna'

That is the easy search side. What I needed were searches like:

'find all users that have red cars'

I started with:

package Main;
use Data::Dumper;

sub printnicks {
    my $it = shift;
    while ( my $bk = $it->next ){
        print $bk->nick, ' ';
    }
    print "\n--------------------\n\n";
}


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

print "Simple join\n";
printnicks($it);

But what it printed in the output was:

Simple join
jolka jolka jolka
--------------------

As you can see it reported duplicates (triplicates?).  To see what was
the reason of that I looked at the generated SQL (remember export
DBIX_CLASS_STORAGE_DBI_DEBUG=1
is your friend there):

SELECT me.nick, me.id FROM person me LEFT JOIN car car ON ( car.usr =
me.id ) WHERE ( car.color = ? ): red at
/home/zby/progs/DBIx-Class-resultset/lib/DBIx/Class/Storage/DBI.pm
line 233.

Yeah - it was searching the full product of the two tables and then
DBIC created a Person object for each row maching our search
operators.  Obviously we need some kind of distinct there.  So here is
my next version:

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

print "Join with distinct\n";
printnicks($it);

Now it prints:

Join with distinct
jolka
--------------------

Looks all right. But when I try count:

 print Model::Person->count(
     { 'car.color' => 'red', },
     {
         distinct => 1,
         join => [ 'car' ],
     }
 );

What I get is:

Unable to ->count with a GROUP BY at
/home/zby/progs/DBIx-Class-resultset/lib/DBIx/Class/ResultSet.pm line
258.

To see why lets look at the SQL generated by the search:

SELECT me.nick, me.id FROM person me LEFT JOIN car car ON ( car.usr =
me.id ) WHERE ( car.color = ? ) GROUP BY me.nick, me.id: red at
/home/zby/progs/DBIx-Class-resultset/lib/DBIx/Class/Storage/DBI.pm
line 233.

This query cannot be simply modified to count all of the resulting records.

To overcome that problem I devised following hack:
my $it = Model::Person->search(
    { 'car.color' => 'red' },
    {
        join => [ 'car' ],
        select => { 'count' => 'distinct me.id' },
        as => [ 'id' ],
    }
);

print "Count: " . $it->next()->id . "\n";

The output was correct: 'Count: 1'

To understand how it works let's look at the generated SQL once again:

SELECT COUNT( distinct me.id ) FROM person me LEFT JOIN car car ON ( car.usr
= me.id ) WHERE ( car.color = ? ): red at
/home/zby/progs/DBIx-Class-resultset/lib/DBIx/Class/Storage/DBI.pm
line 233.

Instead of using GROUP BY to flatten the product of the two tables I
use distinct to count the unique primary keys of the person table. 
Then I use the 'as' attribute to collect the result in the 'id' field
of some bogus Person object (this is the only way I can see to get it
out).

Obviously this can work only when the table has a one column primary key.

-- Zbyszek