laravel get result from first table if second table did not match join condition

Question

I have a requirement where I need to get result from first table if the joined table where condition didn't satisfy.

I have two table food and food_translation,in food_translation table food names where saved in different languages and default name in food table.

if language condition didnt match in food_translation table,then need to get food default_name from food table

$food = DB::table('Program')
->leftJoin('Meals', 'Program.meal_id', '=', 'Meals.meals_id')
->leftJoin('Food', 'Meals.food_id', '=', 'Food.food_id')
->leftJoin('food_translation', 'food_translation.food_id', '=', 'Food.food_id')
->select('Food.food_id','Food.food_name','food_translation.food_ame','Food.default_name')
->where('food_translation.locale', '=', 'ar')->get();

See LEFT JOIN and COALESCE

Strawberry
– Strawberry

2018-05-08 07:21:14 +00:00
Commented May 8, 2018 at 7:21 — Strawberry
– Strawberry, Commented May 8, 2018 at 7:21

Vinay · Accepted Answer · 2018-05-09 03:14:22Z

So when user's current lang. is arabic you want to show the food's arabic name but in case it doesn't exists show them the default name.In this scenario you can never use where clause because regardless of the join a where clause will eliminate any and all rows in the join result that don't match (even if using left joins).If no row in food_translation table has locale = 'ar' therefore in the join result too no row will have locale = 'ar' as a result everything gets eliminated.

What you need to do instead is add the locale ='ar'part in the join condition itself this will give you only those food_translation rows that have locale = 'ar' but without affecting the results of other table joins.Then use COALESCE() (or IFNULL()) operator passing food_translation.food_name as 1st and Food.food_name as the 2nd parameter so if food_translation.food_name is not null it will be used otherwise Food.food_name used.

$food = DB::table('Program')
    ->leftJoin('Meals', 'Program.meal_id', '=', 'Meals.meals_id')
    ->leftJoin('Food', 'Meals.food_id', '=', 'Food.food_id')

        ->leftJoin('food_translation', function ($join) {
                $join->on('food_translation.food_id', '=', 'Food.food_id');
                $join->on('food_translation.locale', '=', 'ar');
        })

    ->select('Food.food_id','Food.food_name', DB::raw('COALESCE(food_translation.food_name,Food.default_name) as foodname')
    )->get();

All in all your raw sql might look like this

SELECT  COALESCE(food_translation.food_name, Food.default_name) as foodname,Food.food_id  
from program

left join meals on meals.meals_id = program.meal_id

left join food on food.food_id = meals.meals_id

LEFT JOIN food_translation on ( food_translation.food_id = food.food_id and food_translation.locale = 'ar' )

inuryyev · Accepted Answer · 2018-05-08 10:32:08Z

1

->orWhere('food_translation.locale', '=', 'ar')

use orWhere, then check result and take food_name.

answered May 8, 2018 at 10:32

inuryyev

1532 silver badges10 bronze badges

Collectives™ on Stack Overflow

laravel get result from first table if second table did not match join condition

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related