How to join 3 tables using a pivot table?

And why is this not correct?

$categories_wiht_foods = Categories::with('subcategories.foods')->get();

If you have the relationship setup correctly... meaning in your Category model you have subcategories relationship method and in the Subcategory you have foods method?

@nakov Because I am using a pivot table categoreis_to_foods

How can I have relationships set up if I have a pivot table? It means there is not category_id on foods table

@martinzeltin but you have a Food model right?

So in your subcategory model having something like this doesn't work?

public function foods()
{
    return $this->belongsToMany(Food::class, 'categories_to_foods');
}   

?

@nakov It gives me this error, I think it is looking for the wrong column?

SQLSTATE[42S22]: Column not found: 1054 Unknown column 'categories_to_foods.sub_categories_id' in 'field list'

@martinzeltin here is the method definition:

belongsToMany($related, $table = null, $foreignPivotKey = null, 
$relatedPivotKey = null, $parentKey = null, $relatedKey = null, $relation = null)

So as a third parameter use subcategory_id:

public function foods()
{
    return $this->belongsToMany(Food::class, 'categories_to_foods', 'subcategory_id');
}  

@nakov ok, that's right. But what about the other Models? How do I need to set up the relationships for Categories and Subcategories model?

So a Category has many Subcategories, right?

Then in the Category model just add this:

public function subcategories()
{
    return $this->hasMany(Subcategory::class);
}

You should have a category_id in the subcategories table. And that will work.

@nakov yes, thank you it is working. But I don't understand why this did not work.... What's wrong with this way?

Categories:

public function subcategories()
        {
            return $this->belongsToMany('App\Models\SubCategories', 'categories_foods', 'category_id', 'subcategory_id');
        }

@martinzeltin because belongsToMany expects a pivot table, so it should map a local key with a key on the pivot table.

What you are trying there is to map two keys from the pivot table instead.

Maybe this will work:

public function subcategories()
{
        return $this->belongsToMany('App\Models\SubCategories', 'categories_foods', 'category_id');
}

@nakov yes, but I have a pivot table. This gave an error

Column not found: 1054 Unknown column categories_foods.sub_categories_id

@martinzeltin you should map a local key then, I gave you the method definition above..

public function subcategories()
{
        return $this->belongsToMany('App\Models\SubCategories', 'categories_foods', 'category_id', 'id');
}

It tries to use sub_categories_id because of the method name. So when you don't provide the keys, Laravel will try to use it's convention which is method name _id to find the relation.

@nakov Unfortunately this put subcategories in the wrong categories...

@martinzeltin you should play with the definition my friend. I don't have your codebase to test. I am just giving you directions :) so follow the documentation + take from what I've said above. I don't know why would you want to get the Subcategories from the pivot table. For me it makes more sense that a Subcategory belongs to a Category not to many Categories. So in that case I will use the hasMany relationship that I've shown you above, just make sure that the subcategory has a category_id, that's it.

@nakov Thanks, I guess another way would be to create a second pivot table for categories -> subcategories relationships.

So far I have ended up with this, which works!

Categories model

public function subcategories()
{
    return $this->hasMany('App\Models\SubCategories', 'category_id', 'id');
}

SubCategories model

public function foods()
{
    return $this->belongsToMany('App\Models\Foods', 'categories_foods', 'subcategory_id', 'food_id');
}