How do I make DBIx::Class join tables using other operators than `=`?

Question

Summary

I've got a table of items that go in pairs. I'd like to self-join it so I can retrieve both sides of the pair in a single query. It's valid SQL (I think), the SQLite engine actually does accept it, but I'm having trouble getting DBIx::Class to bite the bullet.

Minimal example

package Schema::Half;
use parent 'DBIx::Class';
__PACKAGE__->load_components('Core');
__PACKAGE__->table('half');
__PACKAGE__->add_columns(
  whole_id => { data_type => 'INTEGER' },
  half_id  => { data_type => 'CHAR'    },
  data     => { data_type => 'TEXT'    },
 );
__PACKAGE__->has_one(dual => 'Schema::Half', {
  'foreign.whole_id' => 'self.whole_id',
  'foreign.half_id' => 'self.half_id',
  # previous line results in a '='
  # I'd like a '<>'
});

package Schema;
use parent 'DBIx::Class::Schema';
__PACKAGE__->register_class( 'Half', 'Schema::Half' );

package main;
unlink 'join.db';
my $s = Schema->connect('dbi:SQLite:join.db');
$s->deploy;

my $h = $s->resultset('Half');
$h->populate([
  [qw/whole_id half_id data  /],
  [qw/1        L       Bonnie/],
  [qw/1        R       Clyde /],
  [qw/2        L       Tom   /],
  [qw/2        R       Jerry /],
  [qw/3        L       Batman/],
  [qw/3        R       Robin /],
 ]);
$h->search({ 'me.whole_id' => 42 }, { join => 'dual' })->first;

The last line generates the following SQL:

SELECT me.whole_id, me.half_id, me.data
FROM half me
JOIN half dual ON ( dual.half_id = me.half_id AND dual.whole_id = me.whole_id )
WHERE ( me.whole_id = ? )

I'm trying to use DBIx::Class join syntax to get a <> operator between dual.half_id and me.half_id, but haven't managed to so far.

Things I've tried

The documentation hints towards SQL::Abstract-like syntax.

I tried writing the has_one relationship as such:

__PACKAGE__->has_one(dual => 'Schema::Half', {
  'foreign.whole_id' => 'self.whole_id',
  'foreign.half_id' => { '<>' => 'self.half_id' },
});

# Invalid rel cond val HASH(0x959cc28)

Straight SQL behind a stringref doesn't make it either:

__PACKAGE__->has_one(dual => 'Schema::Half', {
  'foreign.whole_id' => 'self.whole_id',
  'foreign.half_id' => \'<> self.half_id',
});

# Invalid rel cond val SCALAR(0x96c10b8)

Workarounds and why they're insufficient to me

I could get the correct SQL to be generated with a complex search() invocation, and no defined relationship. It's quite ugly, with (too) much hardcoded SQL. It has to imitated in a non-factorable way for each specific case where the relationship is traversed.

I could work around the problem by adding an other_half_id column and joining with = on that. It's obviously redundant data.

I even tried to evade said redundancy by adding it through a dedicated view (CREATE VIEW AS SELECT *, opposite_of(side) AS dual FROM half...) Instead of the database schema it's the code that got redundant and ugly, moreso than the search()-based workaround. In the end I wasn't brave enough to get it working.

Wished SQL

Here's the kind of SQL I'm looking for. Please note it's only an example: I really want it done through a relationship so I can use it as a Half ResultSet accessor too in addition to a search()'s join clause.

sqlite> SELECT * 
        FROM half l 
        JOIN half r ON l.whole_id=r.whole_id AND l.half_id<>r.half_id
        WHERE l.half_id='L';
1|L|Bonnie|1|R|Clyde
2|L|Tom|2|R|Jerry
3|L|Batman|3|R|Robin

Side notes

I really am joining to self in my full expanded case too, but I'm pretty sure it's not the problem. I kept it this way for the reduced case here because it also helps keeping the code size small.

I'm persisting on the join/relationship path instead of a complex search() because I've got multiple uses for the association, and I didn't find any "one size fits all" search expression.

Late update

Answering my own question two years later, it used to be a missing functionality that has since then been implemented.

Answer 1

For those still interested by this, it's finally been implemented as of 0.08192 or earlier. (I'm on 0.08192 currently)

One correct syntax would be:

__PACKAGE__->has_one(dual => 'Schema::Half', sub {
  my $args = shift;
  my ($foreign,$self) = @$args{qw(foreign_alias self_alias)};
  return {
    "$foreign.whole_id" => { -ident => "$self.whole_id" },
    "$foreign.half_id" => { '<>' => { -ident => "$self.half_id" } },
  }
});

Trackback: DBIx::Class Extended Relationships on fREW Schmidt's blog where I got to first read about it.

Answer 2

I think that you could do it by creating a new type of relationship extending DBIx::Class::Relationship::Base but it doesn't seem incredibly well documented. Have you considered the possibility of just adding a convenience method on the resultset set for Half that does a ->search({}, { join => ... } and returns the resultset from that to you? It's not introspectable like a relationship but other than that it works pretty much as well. It uses DBIC's ability to chain queries to your advantage.