Equivalent of FORCE INDEX FOR JOIN in Laravel query builder

Question

I'm building a Laravel 5.2 application. I have a native SQL Query that need to be translated to the query builder form, I mean, make the equivalent of the SQL query in the Laravel query builder. Basically, I'm facing a problem when I tried to translate the FORCE INDEX FOR JOIN command, this is the native SQL query:

SELECT some columns...
    FROM table1 AS t1 
        LEFT JOIN table2 AS t2 
        FORCE INDEX FOR JOIN (idx_table2) 
            ON ((t1.messageid = t2.messageid) AND (t2.othercolumn = 1))  
        WHERE something... 

So, by now I have:

$query= DB::table('table1 as t1')
                        ->leftJoin('table2 as t2', 't1.messageid', '=', 't2.messageid')

So, How can I add this line FORCE INDEX FOR JOIN (idx_table2) ?

Zeiler

Answer 1

You can use this syntax:

$query= DB::table('table1 as t1')
                    ->leftJoin(DB::raw('table2 AS t2 FORCE INDEX FOR JOIN (idx_table2)'), 't1.messageid', '=', 't2.messageid')

Trot

It's enough with ->leftJoin(DB::raw('table2 AS t2 FORCE INDEX (idx_table2)'), ...) – Concertina