DBIx, using belongs_to on optional field, insert-by-side-effect in foreign table

smallvaluesof2 has asked for the wisdom of the Perl Monks concerning the following question:

I am using DBIx and am having some issues reading an object from DB that has a belongs_to relationship defined on an optional field. If the optional field is NULL in the DB, the accessor for reading the field attempts to create a new entry in the foreign table with blank/null values instead of (as I would like it) just returning undef.

Apart from not wanting blank values in the foreign table, the subsequent insert-by-side-effect calls will fail as a uniqueness constraint will be violated.

A sample scenario is a car table with an optional radio_type column which is either NULL or holds the foreign key of a radio_type table row.

I'm not sure if I am grappling with default well intentioned behaviour that can be disabled or have just made some basic error. Enlightenment appreciated.

Comment on DBIx, using belongs_to on optional field, insert-by-side-effect in foreign table

Replies are listed 'Best First'.
Re: DBIx, using belongs_to on optional field, insert-by-side-effect in foreign table by kennethk (Abbot) on Jan 19, 2009 at 15:12 UTC
First, to clarify, I am answering this based upon DBIx::Class and the belongs_to method contained in the DBIx::Class::Relationship submodule. Regarding null foreign keys, the documentation says: If the relationship is optional -- i.e. the column containing the foreign key can be NULL -- then the belongs_to relationship does the right thing. Thus, in the example above $obj->author would return undef. However in this case you would probably want to set the join_type attribute so that a LEFT JOIN is done, which makes complex resultsets involving join or prefetch operations work correctly. If I am reading your question correctly, it sounds like this is your difficulty. The solution can be implemented (based on the module documentation): `My::DBIC::Schema::Car->belongs_to(radio => 'My::DBIC::Schema::Radio', 'radio', {join_type => 'left'});` [download] If this doesn't help, post your calling code and we can see if the issue lies there.	[reply] [d/l]
Re^2: DBIx, using belongs_to on optional field, insert-by-side-effect in foreign table by smallvaluesof2 (Novice) on Jan 19, 2009 at 16:06 UTC
Yes, adding a join_type clause fixed the problem. I had seen the documentation you referred to I but had misinterpreted it to mean that NULL optional foreign key fields would just work out of the box and that the left join clauses might be needed in the context of obtaining rows of cars rather than also to moderate side-effects (not knocking what is going on behind the scenes - it's great, just that I don't understand it all). Thank you	[reply]