Im trying to create a 3 table many to many relationship: I have a booking_type table that represents a real estate room booking, that has either a "payment type" in an n:m relationship and a "payment category" n:m relationship too.
So, there should be a n:m has many table that links every booking with its payment categories with its payment type too (so, i.e., the 'holding deposit' category must be paid online while the periodic payment of the room should be paid monthly). We need to manage this throught 3 tables since there are different booking types, wich represents offers, which have really different payment and booking configurations. I hope this little background helps to understand our problem (sorry if my explanation is so messy).
So, we tried to define this in our Model_Booking_Type $_many_many field:
protected static $_many_many = array(
'types' => array(
'key_from' => 'id_booking_type',
'key_through_from' => 'booking_type_id',
'table_through' => 'Categories_Types_Types',
'key_through_to' => 'payment_type_id',
'model_to' => 'Model_Payment_Types',
'key_to' => 'id_payment_type',
'cascade_save' => true,
'cascade_delete' => true,
),
'categories' => array(
'key_from' => 'id_booking_type',
'key_through_from' => 'booking_type_id',
'table_through' => 'Categories_Types_Types',
'key_through_to' => 'payment_category_id',
'model_to' => 'Model_Payment_Categories',
'key_to' => 'id_payment_category',
'cascade_save' => true,
'cascade_delete' => true,
),
);
[We also try with other names for the many to many table, but no one worked].
Our configuration in the payment category model is:
protected static $_many_many = array(
'booking_types' => array(
'key_from' => 'id_payment_category',
'key_through_from' => 'payment_category_id',
'table_through' => 'Categories_Types_Types',
'key_through_to' => 'booking_type_id',
'model_to' => 'Model_Booking_Types',
'key_to' => 'id_booking_type',
'cascade_save' => true,
'cascade_delete' => false,
)
);
while the payment type configuration is analogue.
The controller code that is triyng to do the insert is the next:
Sorry, the post seems too long and there is a lost part; this is the rest of the post:
...When we try the insertion, the error that outcomes is this:
Cannot add or update a child row: a foreign key constraint fails (`database_debug`.`categories_types_types`, CONSTRAINT `payment_category_fk` FOREIGN KEY (`payment_category_id`) REFERENCES `payment_categories` (`id_payment_category`) ON DELETE CASCADE ON UPD) [ INSERT INTO `Categories_Types_Types` (`booking_type_id`, `payment_type_id`) VALUES ('14', '1') ]
Simply seems that save() method by default treats every field of booking_type $_many_many as different row, and by default understants that every many to many relationship is only 2 tables about.
I don't know if someone had this kind of problem before (I didn't manage to find a thread about this) and if fuel ORM can be used to make this kind of relationship natively...
Sorry for my long post, my messy english, and thnaks in advice.
It is a not-supported setup. A through table can only contain the two foreign keys, nothing else.
If you need more columns in the through table (either a third foreign key or data columns), you need to make a model for it, and change the many-many relation to two one-to-many relations, with the new model for the through table in the middle.
Donate
