[CDBI] Many to many help

William Ross will at spanner.org
Mon Aug 1 12:03:13 BST 2005

On 1 Aug 2005, at 11:22, Mike McKay wrote:

> Thanks for the reply Will - I was aiming for your option 2. But I  
> think I am already doing what you recommended. Allow me to snip...
> William Ross wrote:
>> On 1 Aug 2005, at 09:38, Mike McKay wrote:
>>> Here is basically what I am doing:
>>> PassengerRouteJoin->has_a(passenger => "Passenger");
>>> PassengerRouteJoin->has_a(route => "Route");
>>> Route->has_many(passengers => [ "PassengerRouteJoin" =>   
>>> 'passenger' ]);
>>> Passenger->has_many(routes => [ "PassengerRouteJoin" => 'route' ]);
> Note the last line above is the mapping method that you recommended  
> in #2 below (unless I am missing something).

d'oh! I'm sorry. Stupid of me.

> Then I tried to do this:
> my $p = Passenger->create({surname => "Blah", first_name => "Foo"});
> $p->add_to_routes({departure_point => "Lilongwe", destination =>  
> "Likoma"});
> I get:
> destination is not a column of PassengerRouteJoin at /usr/local/ 
> share/perl/5.8.4/Class/DBI/Relationship/HasMany.pm line 89
> Which makes me think the mapping isn't really happening.

You're right. I should have thought it through. The [class => method]  
shortcut in has_many just defines a last-minute mapping method that  
kicks in when the related object is retrieved. In other words, this:

   Passenger->has_many(routes => [ "PassengerRouteJoin" => 'route' ]);

declares a one to many relationship between Passenger and  
PassengerRouteJoin, but when you ask for the PassengerRouteJoin  
objects the 'route' method is called on each one and what you  
actually get back is the Route object from that column. The  
relationship is still between P and PRJ, which explains the error  
message you got.

(I don't use the add_to_* methods and I foolishly assumed that they  
would do the right thing here).

What's really needed is a mended  
&Class::DBI::Relationship::HasMany::_method_add_to that creates both  
the distant related object and the intermediate joining object, if  
that's the right thing to do. That's not hard to do, but I think it's  
the sort of thing that Tony has put to one side in favour of  
recreating the whole columns-and-relationships architecture in a more  
versatile way. If only here were here, we could send him a patch :)

Unfortunately, C::D::R::HasMany is also awkward to override. That  
probably means the only short term option is to write your own  
Passenger->add_route method. It should be very simple, though.

> <snip/>
>> 2. you use the mapping syntax to put the routes() method in the   
>> Passenger class where you want it:
>>     Passenger->has_many(routes => [ 'PassengerRouteJoin' =>  
>> 'route' ]);
>> in which case $p->add_to_routes({...}) should work. Incidentally,   
>> don't create the route object before you pass it in: add_to_foo   
>> always calls create(), so you'll get duplicates.
>> 3. But unless PassengerRouteJoin is a completely empty apart from  
>> the  linking information, you'll probably end up writing a custom   
>> add_route method in the Passenger class that will look a lot like  
>> [1]  above but with more going on. You could consider getting it  
>> over with  now.
> It is a pure linking table.
> CREATE TABLE passenger_route_join (
>  passenger INTEGER NOT NULL REFERENCES passenger,
> );
> Hmm. Maybe I got this table wrong. From the perldoc:
> "We have a multi-column primary key, with each column pointing to  
> another class."
> I don't quite understand multi-column primary key. Did I screw up  
> here?

No, that looks just right. This isn't a multi-column key and you  
should only have 'id' in your Primary column set. But I don't declare  
the relationships at the database level - i just let cdbi do all that  
- so I don't know what effect that will have.

> I really want to get the mapping working so that I can get a  
> passenger object and display its routes easily using the Template  
> Toolkit stuff:

that should all be working fine: it's very simple, and works  
beautifully with TT. The only awkwardness is in storing related objects.



ps. Note to self: coffee first, offer world benefit of wisdom second,  
if at all.

More information about the ClassDBI mailing list