Laravel 4.2 ORM - Querying relation with various tables in between

Question

I have a database schema that goes like this:

Section
  id
  name

Subsection
  id
  name
  section_id

Category
  id
  name
  subsection_id

SubCategory
  id
  name
  category_id

Product
  id
  name
  subcategory_id

As you can see, each table has a foreign key that references the previous table. The problem comes when I try to get, for example, the Section from the current product or get all products from one section. So far I have tried this:

Section::with('product')->find(1)->product;

But I get this:

SQLSTATE[42S22]: Column not found: 1054 Unknown column 'product.section_id' in 'where clause' (SQL: select * from products where products.section_id in (1))

1 - This makes me think I need to set up a section_id in the products table to make this work. Is this correct?

2 - Shouldn't the Laravel ORM automatically go up the table hierarchy from Product to Section and get the results?

3 - Is there a way to do this maintaining my current table structure, I mean, without adding extra fields in the tables for the foreign keys?

Thanks.

Answer 1

1. No that is one way to do it but it isn't the only way.
2. No, how would it know that automatically?
3. I believe so and you can always create a specific query when laravel relationships don't work for you.

Okay first this assumes you have relationships setup on all the models to access the one below it. If that isn't the case you will need to setup the relationships.

Section::with('subsection.category.subcategory.product')->get();

I've never tried such extreme nesting but I believe this will work. The Laravel Docs talk about eager loading and scroll to see the nested example.

Another item that comes to mind is the hasManyThrough relationship. You couldn't do it for this number deep but it may be something you want to look into.

A brief summary from the docs is taking the first three from your example, Section, Subsection, and Category and then in the section class you would have this relationship.

public function category()
{
    return $this->hasManyThrough('Category', 'SubSection');
}

The laravel docs with more information.