Adhoc parameters to joins in DBIx::Class

Update: there are a few updates to the caveats on this post based on the comments by Peter Rabbitson.

I’ve been using DBIx::Class for a couple of years now and I’ve always been impressed with it’s flexibility.  I’ve rarely felt the need to write my own SQL queries because it’s simply so good at it, and it’s generally easy to get it to do what I want.

The one exception to that was custom adhoc joins.  In general DBIx::Class wants to know about how you’re going to join up front.  Anything else tends to require a custom view.  

The other night I realised I could come up with a way to deal with slightly more complex joins while still making the most of all my result definitions.  The extended relationships explained by frew on his blog demonstrate how to add decent join conditions.  The one thing missing was adhoc parameters.  They can be added by providing a bind parameter.  Since relationships don’t traditionally require any extra search parameters, I’d recommend indicating that the relationship isn’t for public consumption, and providing a wrapper method around it.

For example, here is a the relationship in the Result class,

__PACKAGE__->has_many(
  _date_range => 'DBICTest::Schema::CD',
  sub {
    my $args = shift;
    return (
      { "$args->{foreign_alias}.artist" => { -ident => "$args->{self_alias}.artistid" },
        -and => [
            "$args->{foreign_alias}.year"   => { '>' => \"?" },
            "$args->{foreign_alias}.year"   => { '<' => \"?" },
        ],
      }
    );
  }
);

And then a method to exploit it in the ResultSet class.

sub join_date_range
{
    my $self = shift;
    my $start = shift;
    my $end = shift;
    $self->search(undef, {
        join => ['_by_name'],
        bind => [ $start, $end ],
    });
}

Then you can use it like this,

$schema->resultset("Artist")->join_date_range(1990, 1992)->search(...)->all;

You can of course do more complex joins too, even joining up multiple times.  All you need to do is specify all the necessary bind parameters.

The most impressive thing is that the joins work fine even with the chained searches that DBIC is so good at.  You can of course also do search_related and most of the usual links, you just need to specify the bind parameters.  

There are a few of caveats however.  This isn’t strictly speaking an intentional feature, or at least it wasn’t consciously designed to work this way (as far as I know).

  1. Attempting to do a prefetch across the join fails with the error “Can't prefetch has_many _date_range (join cond too complex)”. Update: there is a new version of DBIC in the pipeline that fixes this. Right now that version is 0.08241-TRIAL. The feature you’re probably looking for in the Changes file is “Multiple has_many prefetch” when checking if a new live release has it.
  2. You might have looked at that -and => [] construction and thought that’s a bit pointless, a standard hash would be simpler and achieve the same effect. Unfortunately the bind parameters are positional, and a hash doesn’t have a guaranteed order. That means you need to be extra careful with your query when you have multiple parameters you need to specify, to ensure the binds happen to the correct place holders. Update: as Peter Rabbitson pointed out, it’s not actually that simple. DBIC does try to make sure you have a guaranteed order by sorting the keys of the hashes so that it always produces the same SQL. This means that you probably just need to try it and see which order in which it requires the parameters most of the time.
  3. Update: I was incorrect about not being able to use extra binding information with bind, the syntax Peter Rabbitson suggested works perfectly. i.e. bind => [ [ $opt1 => $val1 ], [ $opt => $val 2 ]… ]The final caveat is that the bind parameters don’t currently take any extra type information. Normally most of the places you are exposed directly to bindings you can specify types in order to help DBIC create the correct query. It doesn’t appear to be possible to provide that information via the bind parameter on a search.

This isn’t strictly a documented feature, but hopefully it’s helpful to a few people. If you’re wondering why you’d need to do this at all, consider the difference between these two queries.

SELECT *
FROM a LEFT JOIN b ON a.id = b.owner_id AND a.name = b.name

and

SELECT *
FROM a LEFT JOIN b ON a.id = b.owner_id
WHERE a.name = b.name

In the course of figuring this out, I also discovered the -ident key which indicates that you’re dealing with something like a column name, and should therefore be quoted if you have column name quoting turned on.  A handy feature to go along with using references to pass in literal bits of SQL.

Advertisements

GDB

I only occasionally use gdb so I end up spending my time relearning it each time.  Hopefully these notes will make that process easier in the future.  This document assumes you already know the s and n commands and how to list the source code (l).  These are the commands I don’t know off by heart, and have found useful. It’s just a subset, but a useful one so far.  It’s also well worth downloading the manual pdf.

To compile a program with the debug symbols use the -ggdb command line switch.

Here is a basic summary of some of the useful commands.

set args [command line args]
r
bt # stack trace
up/down # move up the stack
p # print
p/x # display hex value
p/x (int[5]) *0xffffd320 # display values at address as integers
x address # display contents of memory location
x/16b address # display 16 bytes
x/32cb address # display 16 bytes as characters
x/5i address # display as instructions (i.e. assembly)
x/5i $pc - 6 # displays current code
info registers # display register contents
$pc, $sp # program counter and stack pointer, can also use $eip
set $sp += 4 # add 4 to stack pointer
set var variablename = 4 # set a program variable
set {int}0x800321 = 4 # sets memory location 0x800321 to 4
j 0x32211 # jump to 0x32211
find # search memory
b *0xaddress # set breakpoint at an address (b nnn sets at a source line)

I’ve also been using lxc a lot recently and being able to create an x86 based machine is useful.  This creates a machine named x86 for testing x86 code.  The apt-get line is to be run on the machine once it’s loaded to install gdb and the compilers.

lxc-create -n x86 -t ubuntu -- -a i386
apt-get install build-essential gdb