->related subquery vs. join condition

[ghost]

From docs: Model_Article::query() ->related('author', array('where' => array(array('active', '=', 1), array('status', '=', 'X'))));

The SQL produced by this call uses the 'where' as a subquery, as opposed to another condition of the join. Current result:

"SELECT t0.id AS t0_c0, t1.authors_id AS t1_c0, t1.active AS t1_c1, t1.status AS t1_c2, FROM author AS t0 LEFT JOIN articles AS t1 ON (t0.id = t1.authors_id) WHERE t1.active = 1 AND t1.status = 'X'"

I believe the proper SQL call should not use WHERE sql binding, but instead use these as part of the join.

Proposed result:

"SELECT t0.id AS t0_c0, t1.authors_id AS t1_c0, t1.active AS t1_c1, t1.status AS t1_c2, FROM author AS t0 LEFT JOIN articles AS t1 ON (t0.id = t1.authors_id AND t1.active = 1 AND t1.status = 'X')"

Sorry if the example is not perfect, I removed some extraneous values from a sample model I used to show it. The important part is the last line of each: ON X AND Y vs. ON X where Y.

Source: http://fuelphp.com/docs/packages/orm/crud.html

Thanks, Tony

[WanWizard]

If you want that, you'll have to use 'join_on' instead of 'where'. Which unfortunately is broken at the moment, see #238