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
CodePudding user response:
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();