How can I filter DBIX::Class resultsets with external data?

Question

Using DBIx::Class and I have a resultset which needs to be filtered by data which cannot be generated by SQL. What I need to do is something effectively equivalent to this hypothetical example:

my $resultset     = $schema->resultset('Service')->search(\%search);
my $new_resultset = $resultset->filter( sub {
    my $web_service = shift;
    return $web_service->is_available;
} );

Reading through the docs gives me no clue how to accomplish a strategy like this.

Cromagnon

Answer 1

You can’t really, due to the goals for which DBIC result sets are designed:

• They compile down to SQL and run a single query, which they do no earlier than when you ask for results.
• They are composable.

Allowing filtering by code that runs on the Perl side would make it extremely hairy to achieve those properties, and would hide the fact that such result sets actually run N queries when composed.

Why do you want this, anyway? Why is simply retrieving the results and filtering them yourself insufficient?

• Encapsulation? (Eg. hiding the filtering logic in your business logic layer but kicking off the query in the display logic layer.) Then write a custom ResultSet subclass that has an accessor that runs the query and does the desired filtering.

• Overhead? (Eg. you will reject most results so you don’t want the overhead of creating objects for them.) Then use HashRefInflator.

Sing

We have code which uses resultsets. If we filter the results manually, the code which expects resultsets has to be radically altered. This proposed "filter" solution is the result of much work on our team looking for alternatives but the reasons are too extensive to answer here. – Cromagnon

Answer 2

If you filter the results and end up with a list of rows you can create a new resultset like this: http://search.cpan.org/~abraxxa/DBIx-Class-0.08127/lib/DBIx/Class/Manual/Cookbook.pod#Creating_a_result_set_from_a_set_of_rows.

This may keep things consistent in keeping the results as a resultset but I imagine you would not be able to chain it or use any other resultset methods on it.

Iosep