Table subset searching with a list and DBIx::Class

When searching for a subset of rows in a database table by using a list of entries in one of its columns, one can use the WHERE column_name IN list syntax in an SQL query. How to do this using DBIx::Class wasn’t obvious to me; at least, not by reading the docs. Here’s what I learned.

Where is this list in this database table?
Table data adapted from Beginning Database Design by Clare Churcher

This won’t be new to many people, but it was new to me and I couldn’t find the part of the docs where this is discussed, so I thought I’d write it up here for my future self to find.

Subset selecting in SQL

Imagine this situation: you have a database table from which you want to select a subset of its rows depending upon known values of one of its columns. Take this data for example, which represents possible failure states in an application:

id	name	severity
0	Ok	0
1	Warning	1
2	Critical	2
3	Error	3
4	Degradation	1
5	Mismatch	1
6	Contamination	2
7	Unknown	-1

Now imagine that not all parts of the application use all failure states. Some only need to use Ok, Warning, Critical, and Error, while others use Ok, Degradation, Mismatch and Unknown. How does one pull out the rows only of interest to that specific part of the application? One way to do this would be like so (note: I’m still a bit of an SQL noob, so be nice to me):

SELECT * FROM failure_states WHERE name IN ('Ok', 'Degradation', 'Mismatch', 'Unknown');

That’s cool. Now, I’ve been working more with DBIx::Class recently, having spent the last several years working almost only with Django and its ORM, hence I’m also a DBIx::Class noob. Anyway, I wanted to do this lookup from DBIx::Class and only stumbled across the example mentioned in the search docs for DBIx::Class::ResultSet, i.e. this bit:

my @cds    = $cd_rs->search({ year => 2001 }); # "... WHERE year = 2001"
my $new_rs = $cd_rs->search({ year => 2005 });

my $new_rs = $cd_rs->search([ { year => 2005 }, { year => 2004 } ]);
               # year = 2005 OR year = 2004

Using this pattern to solve the problem above yields code like this:

my @states = FailureState->search(
    [
        { name => 'Ok' },
        { name => 'Degradation' },
        { name => 'Mismatch' },
        { name => 'Unknown' },
    ]
);

That, as one might say in the New Zealand vernacular, is fugling uckly. Putting things another way, there’s a lot of duplication here, which doesn’t make this solution DRY and it’s not a pattern that would scale well. It works, but surely there’s a better way, right?

Simpler subsets in DBIx::Class

It turns out that yes, there is a better way, and as is often the case, I found an appropriate answer on StackOverflow. Also, because this is Perl, there is more than one way to do it. For instance with the = operator within a search() method call:

my @state_names = qw(Ok Degradation Mismatch Unknown);
my @states = FailureState->search(
    {
        name => { '=' => [ @state_names ] }
    }
);

This probably does something like WHERE name = $state_name in the background … I guess? I’m not sure about the details here, so I’m going to be content with waving my arms as an explanation.

Alternatively, one can use a syntax reminiscent of the WHERE column_name IN list syntax:

my @state_names = qw(Ok Degradation Mismatch Unknown);
my @states = FailureState->search(
    {
        name => { -in => [ @state_names ] }
    }
);

But why use several lines when only two are sufficient? One can be more direct and pass an arrayref:

my @state_names = qw(Ok Degradation Mismatch Unknown);
my @states = FailureState->search( { name => \@state_names } );

Nice!

Good enough for now

I’m sure there are other and better ways of doing this. Even so, this change has simplified my code nicely. Also, I learned something new today, which was cool :-)

Support

If you liked this post and want to see more like this, please buy me a coffee!