Love Fuel?    Donate

FuelPHP Forums

Ask your question about FuelPHP in the appropriate forum, or help others by answering their questions.
JOIN on multiple fields
  • 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?

    Any help or guidance is greatly appreciated.

    Thanks!

  • Anybody have any thoughts?

    I was able to get it to work but it's ugly, very ugly:

    // Controller
    $session = Model_Session::query()
                ->related('session_type')
                ->related('attendance')
                ->related('attendance.speakerrelease', array(
                        'join_on' => array(
                           array('profile_id', '=', \DB::expr('`t2`.`profile_id`'))
                        ),
                    ))
                ->get_one();

    I can't be the only one out there who has needed this?
  • HarroHarro
    Accepted Answer
    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.

    Thanks
  • Thought so. I know some effort was put in to handle the aliases at different points, but not everywhere.

Howdy, Stranger!

It looks like you're new here. If you want to get involved, click one of these buttons!

In this Discussion