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

Question

我有 2 个这样结构的表productsID标题plansID产品编号价格类型基本上这个想法是每个产品都有多个价格，每个产品的最后一个计划是它的当前价格，如果它被删除或过期，它将回退到以前的计划因此，如果一个产品有 2 个带有 id(1, 2)的计划，则该计划id = 2将是其当前价格我想展示他们上次计划中的产品 type = off这是 Laravel ORM 生成的 SQL 查询 Eloquentselect * 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 migration

Schema::create('products', function (Blueprint $table) {

    $table->bigIncrements('id');

    $table->string('title');

    $table->timestamps();

});

plans migration

Schema::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 Relationship

public function plans()

{

    return $this->hasMany(Plan::class);

}

Plan Model Relationship

public 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"

            }

        ]

    }

]

希望这可以帮助