I'm trying to join a related table on multiple fields. I can't find anything in the documentation to show it's possible.
Example:
// Attendance Model:
protected static $_has_one = array(
'speakerrelease' => array(
'key_from' => 'session_id',
'model_to' => 'Model_Attendance',
'key_to' => 'session_id',
)
);
// Controller
$session = Model_Session::query()
->related('session_type')
->related('attendance')
->related('attendance.speakerrelease', array(
'join_on' => array(
array('profile_id', '=', 'profile_id')
),
))
->get_one();
The produced query is (shortened the SELECT fields):
SELECT * FROM (SELECT * FROM `sessions` AS `t0` WHERE `t0`.`id` = '3848' LIMIT 1) AS `t0` LEFT JOIN `attendance` AS `t1` ON (`t0`.`id` = `t1`.`session_id`) LEFT JOIN `speaker_releases` AS `t2` ON (`t1`.`session_id` = `t2`.`session_id` AND `t2`.`profile_id` = 'profile_id')
What I'm looking for is:
SELECT * FROM (SELECT * FROM `sessions` AS `t0` WHERE `t0`.`id` = '3848' LIMIT 1) AS `t0` LEFT JOIN `attendance` AS `t1` ON (`t0`.`id` = `t1`.`session_id`) LEFT JOIN `speaker_releases` AS `t2` ON (`t1`.`session_id` = `t2`.`session_id` AND `t1`.`profile_id` = `t2`.`profile_id`)
Is there a way to have ORM join on multiple fields or is my relationship incorrect?
If you want to pass a column name, you need to prevent it from being escaped or quoted as a literal. You need to use DB::expr() for that, like you have found out.
Challenge you have is going to be the prefix, as it is generated. You could try \DB::expr('`attendance.profile_id`').
But... If your attendance table has a compound key (a primary key made up of two fields), you need to define it as such, and ORM will deal with this for you automatically.
Unfortunately using \DB::expr('`attendance.profile_id`') does not work because of the generated alias.
I was able to make a temporary work around by writing a custom method to use the DB class until I can revisit the relations and see if I can better this setup.
Donate
