Searching for NULL and IS NOT NULL

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