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.

Neille

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.

Neille

@JB you should accept your own answer, it's definitvely better than mine now :) – Scraggy

@Scraggy I thought it wasn't possible to accept one's own answer... but I tried and it seems like it just happened anyway. Must have changed since last I looked. – Neille

Why not __PACKAGE__->has_one( dual => __PACKAGE__, ... ) ? – Waldon

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.

Scraggy

I've drifted a bit from this project since then, but I do recall taking a look at this area of the sources and thinking something like "too many assumptions of an '=', never gonna work." The convenience method might turn out to be the best compromise, I'll give it a try as soon as I get back to this. Thanks for the suggestions! – Neille

Answer 3

JB, notice that instead of:

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';

You can write the same query using:

SELECT * 
        FROM half l 
        JOIN half r ON l.whole_id=r.whole_id
        WHERE l.half_id<>r.half_id AND l.half_id='L';

Which will return the same data and is definitely easier to express using DBIx::Class.

Of course, this doesn't answer the question "How do I make DBIx::Class join tables using other operators than =?", but the example you showed doesn't justify such need.

Solley

My example was only that: an example :) As I said in the question, I've found multiple ways to achieve the individual queries I wanted, including the one you rewrote. But I don't need a single query, I've got multiple use cases. Off the top of my head: retrieve all or a filtered set of the pairs (the one you discussed), retrieve all pairs where one side's data satisfies some predicate (e.g., all pairs involving Batman), and retrieve the specific half on the other side of a given one. I can (in fact, did) achieve all of them using search(), but in the current state, it's worse than pure DBI. – Neille

Answer 4

Have you tried:

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

I believe the matching criteria in the relationship definition is the same used for searches.

Eyestrain

I have. First thing. The result was "Invalid rel cond val HASH(0x959cc28)" I'll edit the question to make that snippet a separate block so it stands out better. – Neille

Maybe it doesn't like that for has_one(). Have you tried it with the generic add_relationship()? search.cpan.org/~ash/DBIx-Class-0.08010/lib/DBIx/Class/… – Eyestrain

You got me hoping! :) But no, same results. – Neille

Answer 5

'foreign.half_id' => \'<> self.half_id'

Pericynthion

Invalid rel cond val SCALAR(0x96c10b8) – Neille

Answer 6

Here is how to do it:

...
field => 1,                  # =
otherfield => { '>' => 2 },  # >
...

Orangeman

I'm trying to set up a relationship, and your answer uses constants, so... where exactly should I fit it in my sample minimalized code? – Neille