MANI MANI - 3 months ago 10
PHP Question

Join in Pivot tabel Laravel

I have following tables:

Food:


  • Foodid(pk)

  • FoodName

  • FoodImage

  • Description



Categories:


  • Category_id(pk)

  • CategoryName



Restaurant


  • Res_id(pk)

  • ResName

  • Address



category_food_restaurant


  • Category_id(fk)

  • Food_id(fk)

  • Res_id(fk)



Now I want to show Food items based on category name.For that I make query as:

$Category = DB::table('Food')
->select('Food.Food_id','Food.FoodName',
'Food.FoodImage','Categories.CategoryName')
->join('Categories','Categories.Category_id',
'=','category_food_restauarant.Category_id')
->where('Categories.CategoryName',
'=','Breakfast')->get();


But this gives me error:


SQLSTATE[42S22]: Column not found: 1054 Unknown column 'Food.Category_id' in 'on clause' (SQL: select
Food
.
Food_id
,
Food
.
FoodName
,
Food
.
FoodImage
,
Categories
.
CategoryName
from
Food
inner join
Categories
on
Categories
.
Category_id
=
Food
.
Category_id
where
Categories
.
CategoryName
= Breakfast).


Where is the problem.How I can show food items based on category by using these tables?

Answer

You are joining Food table and Categories table but there is no relation between them directly. So you have to join these two tables through category_food_restaurant. The following should work:

DB::table('category_food_restaurant')
            ->select('Food.Food_id','Food.FoodName',
                    'Food.FoodImage','Categories.CategoryName')
            ->join('Categories','Categories.Category_id',
                    '=','category_food_restaurant.Category_id')
            ->join('Food','Food.Food_id',
                    '=','category_food_restaurant.Food_id')
            ->where('Categories.CategoryName', 
                    '=','Breakfast')->get();