Kohana Database query builder with JOIN & ON & AND

Question

I'm working on a query that uses COALESCE for retrieving localized strings for my current project

The sql works when used as a raw query but i cant seem to get the joins structured in this way with the query builder

LEFT JOIN text as text_se
ON (main_table.text = text_se.id)
AND (text_se.lang_code = 'SV')

This code:

->join(array('text', 'text_se', 'LEFT')
    ->on('main_table.text', '=', 'text_se.id')
    ->on('text_se.lang_code', '=', 'SV')

Only results in

LEFT JOIN text as text_se
ON (main_table.text = text_se.id AND text_se.lang_code = 'SV')

Which doesn't do the same... Have anyone else used AND after the JOIN-statement and can help me with it?

Can you post the full raw query? – badsyntax May 10 '12 at 17:00 — badsyntax, May 10 '12 at 17:00

pogeybait · Answer 1 · 2012-05-11T23:11:15.927

5

Or you could do this....

->join(array('text', 'text_se'), 'LEFT')
    ->on('main_table.text', '=', DB::Expr('text_se.id AND text_se.lang_code = "SV"'))

edited May 11 '12 at 23:11

answered May 11 '12 at 23:01

pogeybait

score 1 · Answer 2 · answered May 10 '12 at 13:42

1

Why not use where:

->join(array('text', 'text_se', 'LEFT')
->on('main_table.text', '=', 'text_se.id')
->where('text_se.lang_code', '=', 'SV')

answered May 10 '12 at 13:42

matino

This may work in this instance, but the where parameter will affect only the where clause. In some instances it's desirable to join tables on multiple conditions to further optimize queries. – Mike Purcell Dec 30 '14 at 00:02
in this case LEFT JOIN will act like INNER JOIN – Kalim Jul 27 '15 at 10:05

score 1 · Answer 3 · answered Dec 30 '14 at 00:00

1

This appears to work in 3.1.x:

->join(array('text', 'text_se', 'LEFT')
->on('main_table.text', '=', DB::expr('text_se.id'))
->on('text_se.lang_code', '=', DB::expr('SV'))

answered Dec 30 '14 at 00:00

Mike Purcell

3 Answers3