2

I'm trying to combine two tables based on if they match a given pair in a many-to-many relation. I already know the SQL statement I'm trying to produce, which is functionally equivalent to the following:

SELECT columnA, columnB, ...
...
JOIN matching_table
    ON ( (matching_table.id1 = table_a.id AND matching_table.id2 = table_b.id) OR
         (matching_table.id1 = table_b.id AND matching_table.id2 = table_a.id) )
...

But I want to produce it using Kohana's query builder for consistency. The problem is I can't seem to find a way of creating a complex ON query. So far all I've got is

DB::select('columnA', 'columnB', ...)
...
    ->join('matching_table')
        ->on('matching_table.id1', '=', 'table_a.id')
        ->on('matching_table.id2', '=', 'table_b.id')
...

and this generates the first AND sequence but I can't seem to put it together with an OR.

Any suggestions?

Mike Cluck
  • 31,869
  • 13
  • 80
  • 91
  • I think that you can find your answer here: http://stackoverflow.com/questions/3286539/kohana-3-orm-how-to-perform-query-with-2-many-to-many-relationships – dzeno Oct 25 '12 at 16:21
  • Sorry but how does that answer the question? That's only showing how to do inner joins. – Mike Cluck Oct 25 '12 at 16:28

1 Answers1

0

You should be able to use where instead of on (not tested though): 

->join('matching_table')
->where_open()
->where('matching_table.id1', '=', 'table_a.id')
->and_where('matching_table.id2', '=', 'table_b.id')
->where_close()
->or_where_open()
->where('matching_table.id1', '=', 'table_b.id')
->and_where('matching_table.id2', '=', 'table_a.id')
->or_where_close()
matino
  • 17,199
  • 8
  • 49
  • 58
  • I'll have to test it myself when I get the chance but, and correct me if I'm wrong, doesn't a `JOIN` require an `ON` clause to go with it? – Mike Cluck Oct 26 '12 at 14:33
  • it depends on the join type - http://stackoverflow.com/questions/354070/sql-join-where-clause-vs-on-clause – matino Oct 28 '12 at 18:01
  • It works for INNER JOIN but not for LEFT JOIN. In LEFT JOIN is not the same to place the conditions in ON clause and WHERE clause. – Delmo Mar 30 '15 at 19:56