Laravel，在Eloquent和Laravel的模型上使用内部联接进行嵌套SQL查询 [英] Laravel, make nested SQL query with inner join on Eloquent and Laravel's Models

问题描述

我有Followng数据库：

I have followng database:

简单来说，用户有很多商店，商店有很多产品，等等。
在雄辩的ORM的帮助下进行查询：

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');
}

最后，产品型号：

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

推荐答案

急于加载您商店的关系（在产品模型中定义为 with（'shop'））

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();

模型

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

这篇关于Laravel，在Eloquent和Laravel的模型上使用内部联接进行嵌套SQL查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！