php - 在 Laravel 中进行了多次日期验证

Question

由于包含日期，我需要重构我的数据关系。我有三个模型:

\App\Product;
discounts()->hasManyThrough('App\Discount', 'App\DiscountProduct', 'product_id','id','id','discount_id');
\App\DiscountProduct;
discount()->belongsTo('App\Discount', 'discount_id');
\App\Discount;
discountProduct()->hasMany('App\DiscountProduct');

<小时/>

1. discounts_product 表:id、discount_id、product_id
2. 折扣表:ID、名称、开始日期、结束日期等。
3. 产品表:ID、名称、价格等

<小时/>

当我调用 $product->with('discounts')结果是这样的

{"id": 1,
 "name": "Wild Blue Shirt",
 "discounts": [{
     "method": "per_product",
     "type": "percentage",
     "nominal": 10,
     "start_date": "2018-03-01 16:34:50", <= this is my problem
     "end_date": "2018-03-31 16:34:50", <= this is my problem
      },...other expired discounts],
},...other products

我的问题是:如何筛选每种产品的当前(今天)折扣？

我已经尝试过这个:

\App\DiscountProduct;
currentDiscount() {
   return $this->belongsTo("App\Discount", 'discount_id')->whereRaw('now() BETWEEN start_date AND end_date');
};

然后

$product->with('discounts.currentDiscount'); <= doesn't worked

<小时/>

这对我有用:

$product_list = $products->with('variants')->with('discounts')->get();
$product_list->transform(function($product) use ($discounts){
   $data = $product;
   $currentDiscount = collect($product->discounts)->map(function($discount){
       $data = null;
       if($discount['end_date'] > now()){
            $data = $discount;
       };
       return $data;
       });
    $data->currentDiscount = $currentDiscount->filter()->first();
    return $data;
});

但问题是我无法使用 paginate() 功能，使用 forpage() 太痛苦了

否则这个原始数据库也可以工作:

$products = DB::table('products')
->selectRaw("products.id, products.name, products.price, COALESCE(active_discount.discount_amount,0) AS discount")
->leftJoin(DB::raw("(discount_products.product_id, CAST(IF(discounts.`type`='percentage', ROUND((discounts.nominal/100)*products.price,0) ,discounts.nominal) AS UNSIGNED) AS discount_amount FROM discount_products JOIN discounts ON discount_products.discount_id=discounts.id JOIN products ON discount_products.product_id=products.id WHERE NOW() BETWEEN discounts.start_date AND discounts.end_date ) active_discount"),'products.id', '=', 'active_discount.product_id'));

我不认为上述所有方法都是最好的解决方案。请帮忙。

Answer 1

我认为你的问题[分页/等]是因为你发出原始数据库请求而不是使用 Eloquent。

你可以constrain Eager Loading :

Constraining Eager Loads

Sometimes you may wish to eager load a relationship, but also specify additional query constraints for the eager loading query. Here's an example:

$users = App\User::with(['posts' => function ($query) {
    $query->where('title', 'like', '%first%');

}])->get();

In this example, Eloquent will only eager load posts where the post's title column contains the word first. Of course, you may call other query builder methods to further customize the eager loading operation:

$users = App\User::with(['posts' => function ($query) {
    $query->orderBy('created_at', 'desc');

}])->get();

因此，就您的情况而言，您可以这样做(尚未测试，但这应该可行):

$product->with(['discounts' => function ($query) {

        $query->where(['start_date' , '<', now()] , ['end_date', '>' , now()]);

        }])->paginate(10);

您还可以在模型中直接在关系中设置它:

public function currentDiscount()
    {
        return $this->belongsTo('App\Discount', 'discount_id')
            ->where(
                ['start_date', '<', now()],
                ['end_date', '>', now()]
            );
    }