如何从3个表Laravel获取数据

Question

I'm trying to make query for my Laravel model. I need all products from subcategories which belongs to parent category with id = 1 and I need also reviews belongs to this products. How can I got it? I tried by Category model:

 public function scopeForProduct($query, $id)
{
    $query->where('parent_id', $id)->join('products', 'products.category_id', '=', 'categories.id');
}

public function scopeWithProductPhoto($query)
{
    $query->select('products.*');
}

My db: looks like:

Categories:
id | parent_id | name | desc
1     0            App   -
2     1          BBQ    subcat of app

Products:
id | category_id | name | description | photo | partner_link
1       2            Item

Reviews:
id | user_id | product_id | description | rating
1       3          1           -           5

I need to get all products with reviews from subcategories that belongs for parent category (parent_id = 1 for example).

But It doesn't work.

Answer 1

You can use eloquent relationships for this: http://laravel.com/docs/eloquent#relationships

Product model:

public function reviews()
{
    return $this->hasMany('Review');
}

public function category()
{
    return $this->belongsTo('Category');
}

Category model:

public function products()
{
    return $this->hasMany('Product');
}

Review model:

public function product()
{
    return $this->belongsTo('Product');
}

Query:

$parent_id = 1;

$category = Category::with('products.reviews')
whereHas('products', function($q)
{
    $q->has('reviews');
})
->where('parent_id','=',$parent_id);
->get();

This only selects the category which has parent_id 1. And also only if it has products with reviews in it. If no products with reviews are there it returns an empty array.