[Dbix-class] belongs_to with non-foreign keys

[Matt S Trout]

On Fri, Jun 22, 2007 at 03:28:20PM -0400, Christopher Heschong wrote:
> So I have this search:
> 
> my $addr = 16843009;
> 
> $rs->search( {
>             address => { '<=', $addr },
>             $addr => \'<= (address - 1 + (2 << 31 - bits))'
> });
> 
> 
> (Essentially, this finds the parent networks of an IP address whose int
> val is $addr (aka 1.1.1.1))
> 
> And I want to turn it into a join (so I can prefetch it), but I'm running
> into problems.  I've tried something like this:
> 
> __PACKAGE__->belongs_to( parent => 'MyApp::DB::Network',
>     {
>         'foreign.address'      => { '<=', 'self.address' },
>         'self.address'         => \'<= (foreign.address - 1 + (2 << 31 -
> foreign.bits))'
>     },
>     { join     => [qw(network)] }
> );
> 
> 
> but it looks like DBIx::Class joins won't accept anything but /^foreign./
> named keys.  Any ideas on how I could get something like this to work?

Short answer: not easily in the current release.

Slightly longer answer: you can add a where => attr to your rel defs, which
might get you a bit further. Also, I don't think that join => is doing
anything useful.

Long answer: This strikes me as the wrong solution - it doesn't look like
it's really indexable so the join is going to be horribly inefficient. Maybe
you'd be better off with a trigger that sets a parent field on update, maybe
there's another way to denormalise this to be saner ... how about storing
the top-end address as well so you can do it as a join with a BETWEEN, thus
rendering it amenable to fairly quick lookup on a range index somehow?

-- 
      Matt S Trout       Need help with your Catalyst or DBIx::Class project?
   Technical Director    Want a managed development or deployment platform?
 Shadowcat Systems Ltd.  Contact mst (at) shadowcatsystems.co.uk for a quote
http://chainsawblues.vox.com/             http://www.shadowcatsystems.co.uk/