Searching for NULL and IS NOT NULL

From ClassDBI

PostgreSQL prior to 7.2 used to evaluate NULL = NULL as TRUE in a boolean context, e.g. where clause.

So you could do:

Music::Track->search(album => undef) to get tracks not allocated to an album.

Since 7.2 they have changed the default behaviour to NULL = NULL as FALSE, which is (arguably / more) correct.

You can also SET transform_equals_null to TRUE in the database to restore the old behaviour, but I assume this affects other users.

You can use Class::DBI::AbstractSearch, but that doesn't hook into the methods generated by "has_many".

I have over-ridden the _do_search in my subclass like this:

sub _do_search {
        my ($proto, $search_type, @args) = @_;
        my $class = ref $proto || $proto;

        @args = %{ $args[0] } if ref $args[0] eq "HASH";
        my (@cols, @vals);
        my $search_opts = @args % 2 ? pop @args : {};
        while (my ($col, $val) = splice @args, 0, 2) {
                my $column = $class->find_column($col)
                        || (first { $_->accessor eq $col } $class->columns)
                        || $class->_croak("$col is not a column of $class");
                push @cols, $column;
                push @vals, $class->_deflated_column($column, $val);
              }
        my $frag;
        if ($search_type eq '=') {
                $frag = join " AND ", map "($_ = ? OR (? IS NULL AND $_ IS NULL))", @cols;
                @vals = map {($_,$_)} @vals;
        } else {
                $frag = join " AND ", map "$_ $search_type ?", @cols;
        }
        $frag .= " ORDER BY $search_opts->{order_by}" if $search_opts->{order_by};
        return $class->sth_to_objects($class->sql_Retrieve($frag), \@vals);
}

to do it the right way.

IS NOT NULL

Looking for "IS NOT NULL" is simple. But since I had such a hard time finding my answer, I figured I'd document it. In your CDBI class you include Class::DBI::AbstractSearch

   #!/usr/bin/perl

   use Some::CDBI;

   my $sql = 'is not null';

   my @rows = Some::CDBI->search_where(
      some_column => \$sql
   );