Re: [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))'
});



val is $addr (aka 1.1.1.1))


into problems.  I've tried something like this:

__PACKAGE__->belongs_to( parent => 'MyApp::DB::Network',
    {
        'foreign.address'      => { '<=', 'self.address' },

foreign.bits))'
    },
    { join     => [qw(network)] }
);

(Essentially, this finds the parent networks of an IP address whose int And I want to turn it into a join (so I can prefetch it), but I'm running 'self.address' => \'<= (foreign.address - 1 + (2 << 31 - 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.


might get you a bit further. Also, I don't think that join => is doing
anything useful.


rendering it amenable to fairly quick lookup on a range index somehow?

Slightly longer answer: you can add a where => attr to your rel defs, which 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

--
/chris/

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!

smime.p7s
Description: S/MIME cryptographic signature

_______________________________________________
List: http://lists.rawmode.org/cgi-bin/mailman/listinfo/dbix-class
Wiki: http://dbix-class.shadowcatsystems.co.uk/
IRC: irc.perl.org#dbix-class
SVN: http://dev.catalyst.perl.org/repos/bast/trunk/DBIx-Class/
Searchable Archive: http://www.mail-archive.com/dbix-class@lists.rawmode.org/