ORM: Related 'where' clause outputs flawed SQL query

[jmbier]

Short description: Following these directions: http://www.fuelphp.com/docs/packages/orm/relations/intro.html#/usage_rel_conditions When you specify 'where' in a related model the SQL outputs 'where' when it should be 'on', the result is that you only get results that match the subquery where clause (essentially the same as a right join).
See: http://stackoverflow.com/questions/4752455/left-join-with-where-clause

Original forum post: http://fuelphp.com/forums/discussion/11763/orm-relation-with-039where039-clause-doesn039t-result-in-a-left-join#Item_5

[WanWizard]

What you want here is an outer join: you want to include the parent record even if there are no related children.

If you want the selection criteria to be part of the ON clause, and not the WHERE clause, then define it so:

        $entries = Model_Employee::find('all', array(
            'related' => array(
                'tips' => array(
                    'join_type' => 'left',
                    'join_on' => array(
                        array('date', '>=', $sdate),
                        array('date', '<=', $edate),
                    ),
                ),
            )
        ));

This feature doesn't seem to be documented, so I'll correct that.