[Dbix-class] belongs_to with non-foreign keys

[Christopher Heschong]

On Jun 23, 2007, at 11:52 AM, Matt S Trout wrote:

> 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?
>

So it turns out that if you have, say a bunch of networks stored like  
this, getting the Children is pretty much impossible the way I was  
trying to do this.  I did what you said and I trigger an update of a  
parent_id field on insert.  And on the parent delete/update/insert  
too of course.  Works beautifully, much faster...

Now I just have to figure out why my prefetch isn't actually keeping  
me from doing fewer DB queries.  Thanks!

--
/chris/
-------------- next part --------------
A non-text attachment was scrubbed...
Name: smime.p7s
Type: application/pkcs7-signature
Size: 2409 bytes
Desc: not available
Url : http://lists.scsys.co.uk/pipermail/dbix-class/attachments/20070623/ca891836/smime.bin