How can I filter a Perl DBIx recordset with 2 conditions on the same column?

Question

I'm getting my feet wet in DBIx::Class — loving it so far.

One problem I am running into is that I want to query records, filtering out records that aren't in a certain date range.

It took me a while to find out how to do a <= type of match instead of an equality match:

my $start_criteria = ">= $start_date";
my $end_criteria = "<= $end_date";
my $result   = $schema->resultset('MyTable')->search(
  {
    'status_date' => \$start_criteria,
    'status_date' => \$end_criteria,
  });

The obvious problem with this is that since the filters are in a hash, I am overwriting the value for "status_date", and am only searching where the status_date <= $end_date. The SQL that gets executed is:

SELECT me.* from MyTable me where status_date <= '9999-12-31'

I've searched CPAN, Google and SO and haven't been able to figure out how to apply 2 conditions to the same column. All documentation I've been able to find shows how to filter on more than 1 column, but not 2 conditions on the same column.

I'm sure I'm missing something obvious. Can someone here point it out to me?

Bedouin

Answer 1

IIRC, you should be able to pass an array reference of multiple search conditions (each in its own hashref.) For example:

my $result = $schema->resultset('MyTable')->search(
  [ { 'status_date' => \$start_criteria },
    { 'status_date' => \$end_criteria },
  ]
);

Edit: Oops, nervermind. That does an OR, as opposed to an AND.

It looks like the right way to do it is to supply a hashref for a single status_date:

my $result = $schema->resultset('MyTable')->search(
    { status_date => { '>='  => $start_date,  '<='  => $end_date } } 
);

This stuff is documented in SQL::Abstract, which DBIC uses under the hood.

Phosphide

Perfect! That is exactly what I needed! If only that were documented better... Thanks much, friedo, I apreciate it! – Bedouin

Answer 2

There is BETWEEN in SQL and in DBIx::Class it's supported:

my $result = $schema->resultset('MyTable')
   ->search({status_date => {between => [$start_date,$end_date]}});

Greenaway