FuelPHP Forums

Using ORM the currently generated SQL is:

SELECT `t0`.`id` AS `t0_c0`, `t0`.`name` AS `t0_c1`, `t0`.`is_default` AS `t0_c2`, `t0`.`created_by` AS `t0_c3`
, `t0`.`deleted` AS `t0_c4`, `t0`.`created_at` AS `t0_c5`, `t0`.`updated_at` AS `t0_c6`, `t1`.`id` AS `t1_c0`
, `t1`.`interviewprenatal_id` AS `t1_c1`, `t1`.`prenataltopics_id` AS `t1_c2`, `t1`.`is_checked` AS `t1_c3`
, `t1`.`created_at` AS `t1_c4`, `t1`.`updated_at` AS `t1_c5` 
FROM `prenataltopics` AS `t0` 
LEFT JOIN `interviewprenataltopics` AS `t1` ON (`t0`.`id` = `t1`.`prenataltopics_id`) 
WHERE `t0`.`is_default` = '1' AND `t0`.`deleted` = '0'

How do I add an additional clause to the left join so that it would generate as:

LEFT JOIN `interviewprenataltopics` AS `t1` ON (`t0`.`id` = `t1`.`prenataltopics_id`)  and `t1`.`interviewprenatal_id` = 2

Would love to be able to do so by doing something like:

  $PrenatalFactors = OrmPrenatalfactor::find("all", array('where' => array('is_default' => '1', 'deleted' => '0')
   ,'related' => array(
    'interviewprenatalfactor' => array(
     'join_on' => array( array('interviewprenatal_id','=',$Patient->interviewprenatal->id)),
    ),
   )
  ));

Or a more inefficient but good enough for this use would be having an "is null" option:

  $PrenatalFactors = OrmPrenatalfactor::find()
   ->related('interviewprenatalfactor')
   ->where(array('is_default' => '1', 'deleted' => '0'))
   ->where_open()
   ->where('interviewprenatal_id', '=', $Patient->interviewprenatal->id)
   ->or_where('interviewprenatal', 'is null')
   ->where_close()
   ->get();

But table aliasing is an issue with that option. Right now got it to work by loading the main table first and iterating over the results and loading the left join table 1 by 1.