dbic record don't add up

ag4ve has asked for the wisdom of the Perl Monks concerning the following question:

i am generating a report with numbers of records. however when i do this:


### unique transits for org
      my $orguniq_rs = $org_rs->search( {}, {
                              group_by => [ qw/name/ ]
               }  );         

### unique transits for org type cargo
      my $one_rs = $orguniq_rs->search( {
                           'type' => { '=' => 'one' }
                        } );

### unique transits for org type tanker
      my $two_rs = $orguniq_rs->search( {
                           'type' => { '=' => 'two' }
                        } );

### unique transits for org type other
      my $other_rs = $orguniq_rs->search( {
                           'type' => { '!=' => [ 'one', 'two' ] }
                        } );
[download]

now, what should happen is, one + two + other = uniqorg (ie, total) however, other is showing up with the same number of records as the total. i've tried to do:

-or [ 'type' => { '!=' => [ 'one', 'two' ] } ]
[download]

and -and [ .. ]. if anyone has *any* suggestions, i'd appreciate it.

Comment on dbic record don't add up Select or Download Code

Replies are listed 'Best First'.
Re: dbic record don't add up by moritz (Cardinal) on Apr 26, 2011 at 14:14 UTC
other is showing up with the same number of records as the total Sounds like it generates SQL like `type != 'one' OR type != 'two'` (should be AND, and is always true with OR). You can check that by setting the DBIC_TRACE environment variable to a true value, and running the queries again. Another idea: can `type` be NULL? If yes, this could lead to counts being off, because (iirc) NULL = 'a' and NULL != 'a' both return false. Perl 6 - second systems done right	[reply] [d/l] [select]

Replies are listed 'Best First'.

Re: dbic record don't add up
by moritz (Cardinal) on Apr 26, 2011 at 14:14 UTC

other is showing up with the same number of records as the total

Sounds like it generates SQL like type != 'one' OR type != 'two' (should be AND, and is always true with OR). You can check that by setting the DBIC_TRACE environment variable to a true value, and running the queries again.

Another idea: can type be NULL?

If yes, this could lead to counts being off, because (iirc) NULL = 'a' and NULL != 'a' both return false.

Perl 6 - second systems done right

[reply]
[d/l]
[select]