我有 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();
2 回答
千巷貓影
TA貢獻1829條經驗 獲得超7個贊
嘗試使用 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
');
忽然笑
TA貢獻1806條經驗 獲得超5個贊
你應該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"
}
]
}
]
希望這可以幫助
- 2 回答
- 0 關注
- 261 瀏覽
添加回答
舉報
0/150
提交
取消