Laravel query to join different tables based on a condition

Question

How to join multiple tables based on a condition. I have 3 tables.

• Transactions table
• advertisement table
• offerrequests table

Transaction table has relation with advertisement and offerrequests table by the fields - is_sell and post_id

if is_sell = 1 then
  // post id is id in advertisement table
if is_sell is 0 then
  // post id is id in offerrequests table

column country is only presnt in advertisemnt and offerrequests table. so i need to join the tables to get country for each transaction

I got the result using mysql query like :

SELECT transactions.id , IF( transactions.is_sell = '1', advertisements.country, offerrequests. country ) AS country 
FROM transactions 
LEFT JOIN advertisements ON ( advertisements.id = transactions.post_id )
LEFT JOIN offerrequests ON ( offerrequests.id = transactions.post_id );

Can anyone help me to get the laravel query corresponding to same

Answer 1

You can use

$transactions = DB::table('transactions')
    ->select('transactions.id', DB::raw("IF(transactions.is_sell = '1', advertisements.country, offerrequests.country) as country"))
    ->leftJoin('advertisements', function ($join) {
        $join->on('advertisements.id', '=', 'transactions.post_id');
    })
    ->leftJoin('offerrequests', function ($join) {
        $join->on('offerrequests.id', '=', 'transactions.post_id');
    })
    ->get();