Building query to select from 3 tables in Laravel

Question

Normally I know how to query and join three tables but here I can't figured it how to happen. The thing is that I have followed 3 tables:

category -> columns

id
name 
image

sub-category -> columns

id
table1_id
name
image

Sub-sub-category -> columns

id
table2_id
name
image

So image can be added only to Category or to Category->Sub-Category or to third level Category->Sub-Category->Sub-Sub-Category

What I'm trying to do is when I show ALL images on page to show also to which category they are added.

I've made all relations in my models but I can't figured it out how to query exactly. Currently only querying all images like

SubSubCategories::all();

Example:

I have main category(Country) which has sub-category (district) which has sub-sub-category(city).

image1 is added to Sub-Sub-Category with name city. When images are listed I want to show

images1 added to Country->District->City.

Can someone show me example query or maybe my relations eg. columns in tables are wrong?

This is what I've tried so far

    $subsubcategories = DB::table('sub_sub_category')
                ->join('sub_category', 'sub_sub_category.sub_cat_id', '=', 'sub_category.sub_cat_id')
                ->join('category', 'category.category_id', '=', 'sub_category.sub_cat_id')
                ->select('sib_sub_category.*', 'sub_category.*', 'category.*')
                ->get();

Then in my view

{{ $subsubcategory->sub_category->sub_cat_name }} > {{ $subsubcategory->sub_sub_cat_name }}

error

Undefined property: stdClass::$sub_category

Valen Tin · Accepted Answer · 2017-04-25 19:39:54Z

2

$subsubcategories = DB::table('category')
            ->join('category', 'category.id', '=', 'sub_category.table1_id')
            ->join('sub_category', 'sub_category.id', '=', 'sub_sub_category.table2_id')
            ->select('category.*', 'sub_category.*', 'sub_sub_category.*')
            ->get();

answered Apr 25, 2017 at 19:39

Valen Tin

1691 silver badge12 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

S.I. · Accepted Answer · 2017-04-25 08:45:21Z

0

Actually you are almost there. Just remove the sub_category from the view and will work.

{{ $subsubcategory->sub_cat_name }} > {{ $subsubcategory->sub_sub_cat_name }}

This is happen because your array currently is looks like

object(stdClass) {
  ["id"]=> int(1)
  ["table2_id"]=> int(1)
  ["table1_id"]=> int(1)

  ...
  // all other columns
}

So actually they belongs to $subsubcategory and no need to bring sub_category and category in the view. Just display them in your $subsubcategory.

answered Apr 25, 2017 at 8:45

S.I.

3,38112 gold badges53 silver badges87 bronze badges

Collectives™ on Stack Overflow

Building query to select from 3 tables in Laravel

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