perl DBIx::Class have a default filter for all searches on a table

Question

I have a bunch of DBIx::Class Result classes created from my database schema by dbicdump / DBIx::Class::Schema::Loader

I need to add an is_deleted boolean column to one of the tables, and to filter out deleted records in all the existing searches and JOINs.

Unfortunately there are 30 or 40 places in the sprawling perl app which directly use the table in question, and at least the same number of places which JOIN to it via prefetch or join attributes to search(). Changing them all manually is possible, but very time consuming.

Is there a way to add a default WHERE clause to all queries which SELECT from or JOIN to a particular table?

I'm after some way to be able to call resultset('MyTable')->search({},{}) and have WHERE is_deleted = 0 added to all the queries. Plus have the same is_deleted = 0 filter applied when prefetch => [qw(my_table)], is used.

Answer 1

Yes, you can subclass your resultset class and override the search() method to add your search criteria.

package MyApp::Schema::Resultset::MyTable;

use strict;
use warnings;

use base 'DBIx::Class::Resultset';

sub search {
  my $self = shift;

  my ($cols, $opts) = @_;

  $cols //= {};

  $cols->{is_deleted} //= 0;

  return $self->next::method($cols, $opts);
}

1;

Note:

1. We only set is_deleted if it's not already set - this allows us to look for rows with is_deleted set to 1 if we ever need to do that.
2. DBIx::Class uses Class::C3 for method resolution, so we use next::method() instead of SUPER::method() to call the superclass method.
3. Any search options are passed through to the superclass method unchanged.