CODEWITHSUNDEEP
laraveleloquent
Aleksej_Shherbak
Aleksej_Shherbak

Reputation: 3088

Laravel, make nested SQL query with inner join on Eloquent and Laravel's Models

I have followng database:

enter image description here

enter image description here

Simply speaking, Users have many shops, Shops have many products etc. I need to make this query with the help of Eloquent ORM:

SELECT * FROM tmp.shops
INNER JOIN
    (SELECT * FROM tmp.products
    WHERE tmp.products.shop_id IN
    (SELECT id FROM shops where user_id = 1))  as nested_query
ON  tmp.shops.id = nested_query.shop_id;

I need to catch information about each product in user's shop and info about shop.

About my Models. This is relation with Shop in User model

/**
* @return \Illuminate\Database\Eloquent\Relations\HasMany
*/
public function shop()
{
    return $this->hasMany('App\Shop');
}

And this is Relations in Shop model:

public function user()
{
    return $this->belongsTo('App\User');
}

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

Finaly, Product model:

public function shop()
{
    return $this->belongsTo('App\Shop');
}

Upvotes: 1

Views: 694

Answers (1)

M Khalid Junaid
M Khalid Junaid

Reputation: 64496

Eager load your shop's relation(with('shop')) defined in your product's model as

$user_id=1;
$products = Product::with('shop')
                   ->whereHas('shop.user', function ($query) use($user_id) {
                        $query->where('id', '=', $user_id);
                    })
                   ->get();

Models

class Product extends Model {

    public function shop() {
        return $this->belongsTo('Shop', 'shop_id');
    }
}

class Shop extends Model {
    public function user() {
        return $this->belongsTo('User', 'user_id');
    }
    public function products() {
        return $this->hasMany('Product', 'shop_id');
    }
}

Now when you iterate products you will get shop details object in each product object

Upvotes: 2

Related Questions