mysql查询：获取多表选择相关表的最后一行

Question

我有 2 个这样结构的表

products

ID

标题

plans

ID

产品编号

价格

类型

基本上这个想法是每个产品都有多个价格，每个产品的最后一个计划是它的当前价格，如果它被删除或过期，它将回退到以前的计划

因此，如果一个产品有 2 个带有 id(1, 2)的计划，则该计划id = 2将是其当前价格

我想展示他们上次计划中的产品 type = off

这是 Laravel ORM 生成的 SQL 查询 Eloquent

select * from `products` where exists

        (select * from `plans` where `products`.`id` = `plans`.`product_id`

                and `type` = 'off' 

                and `plans`.`deleted_at` is null) 

        and `products`.`deleted_at` is null

问题是它不检查它会在所有计划中搜索的最后一个/当前计划......所以即使id = 2类型的计划没有关闭并且如果plan.id = 1类型关闭我仍然会在查询中使用这个产品

是php代码：

$wonder_product = Product::whereHas('CurrentPlan', function ($q) {

    $q->where('type', 'off');

})->get();

Answer 1

尝试使用 GROUP BY 子查询：$wonder_product = Product::whereHas('CurrentPlan', function ($q) {    $q->where('type', 'off')    ->whereIn('id', function ($subquery) {        $subquery        ->from(with(new CurrentPlan)->getTable())        ->select(DB:raw('MAX(id)'))        ->groupBy('product_id');    });})->get();或者，如果您可以使用原始子查询：$wonder_product = Product::whereHas('CurrentPlan', function ($q) {    $q->where('type', 'off')      ->whereRaw('id in (select max(id) from plans group by product_id)')})->get();如果我没有错，这两种方法都应该生成这样的查询：select * from `products`where exists (        select * from `plans`        where `products`.`id` = `plans`.`product_id`          and `type` = 'off'           and `plans`.`deleted_at` is null          and id in (select max(id) from plans group by product_id)  )   and `products`.`deleted_at` is null但如果是我，我可能会写一个这样的原始查询：$wonder_product = Product::hydrateRaw('    select products.*    from products    where 'off' = (      select plans.type      from plans      where plans.product_id = products.id        and plans.deleted_at is null      order by plans.id desc      limit 1    )    and products.deleted_at is null');

Answer 2

你应该whereDoesntHave改用return Product::whereDoesntHave('plans', function ($q) {    $q->where('type', 'off');})->with('plans')->get();工作示例：products migrationSchema::create('products', function (Blueprint $table) {    $table->bigIncrements('id');    $table->string('title');    $table->timestamps();});plans migrationSchema::create('plans', function (Blueprint $table) {    $table->bigIncrements('id');    $table->unsignedBigInteger('product_id');    $table->foreign('product_id')->references('id')->on('products')                                 ->onDelete('cascade');    $table->decimal('price');    $table->string('type');    $table->timestamps();});Product Model Relationshippublic function plans(){    return $this->hasMany(Plan::class);}Plan Model Relationshippublic function product(){    return $this->belongsTo(Product::class);}Sample Data Seeder$productWithOnePlanOff = Product::create([    'title' => 'A product with one of its plans off']);$productWithOnePlanOff->plans()->createMany([    ['price' => rand(1, 50), 'type' => 'off'],    ['price' => rand(50, 100), 'type' => 'on']]);$productWithNoPlanOff = Product::create([    'title' => 'A product with none of its plans off']);$productWithNoPlanOff->plans()->createMany([    ['price' => rand(1, 50), 'type' => 'on'],    ['price' => rand(50, 100), 'type' => 'on']]);查询部分和结果WhereHas寻找一个模型，它的任何相关模型都与查询条件匹配return Product::whereHas('plans', function ($q) {    $q->where('type', 'off');})->with('plans')->get();结果[    {        "id": 1,        "title": "A product with one of its plans off",        "created_at": "2019-09-03 16:30:30",        "updated_at": "2019-09-03 16:30:30",        "plans": [            {                "id": 1,                "product_id": 1,                "price": "46.00",                "type": "off",                "created_at": "2019-09-03 16:30:30",                "updated_at": "2019-09-03 16:30:30"            },            {                "id": 2,                "product_id": 1,                "price": "50.00",                "type": "on",                "created_at": "2019-09-03 16:30:30",                "updated_at": "2019-09-03 16:30:30"            }        ]    }]虽然查询whereDoesntHave 确保其相关模型的NONE与查询的条件匹配return Product::whereDoesntHave('plans', function ($q) {    $q->where('type', 'off');})->with('plans')->get();结果[    {        "id": 2,        "title": "A product with none of its plans off",        "created_at": "2019-09-03 16:30:30",        "updated_at": "2019-09-03 16:30:30",        "plans": [            {                "id": 3,                "product_id": 2,                "price": "49.00",                "type": "on",                "created_at": "2019-09-03 16:30:30",                "updated_at": "2019-09-03 16:30:30"            },            {                "id": 4,                "product_id": 2,                "price": "93.00",                "type": "on",                "created_at": "2019-09-03 16:30:30",                "updated_at": "2019-09-03 16:30:30"            }        ]    }]希望这可以帮助