特定时间段的用户订单和发货计数表

Question

在laravel应用程序中，我想做一个报告来统计每个用户在特定时期的订单和发货请求我创建了两个输入to和from来选择日期然后，我想查询获取用户和计数的订单和出货计数表我怎样才能做到呢?在用户模型中，我有两个关系，比如

public function orders()
{
return $this->hasMany('AppModelsOrder' , 'user_id');
}

public function shippings()
{
return $this->hasMany(Shipping::class);
}

订单模型中的

public function user(){
return $this->belongsTo('AppModelsUser' , 'user_id')
->withDefault(new User());
}

public function user(){
return $this->belongsTo('AppModelsUser' , 'user_id')
->withDefault(new User());
} 

我想写很多查询，这是最后一个

if (!empty($from) || !empty($to)) {
$report = User::whereHas('shippings',function ($q) use ($from,$to) {
$q->whereBetween('created_at',[$from,$to])->groupBy('created_at')->get();
})->orWhereHas('orders',function ($q) use($from,$to){
$q->whereBetween('created_at',[$from,$to])->groupBy('created_at')->get();
})->orderByRaw('orders_count + shippings_count DESC')
->withCount(['orders','shippings'])->paginate(25);
}else {
$report = User::withCount(['orders','shippings'])->orderBy('shippings_count','desc')->paginate(25);
}

有谁能帮我吗?

获取每个用户在特定时期的订单和发货，我选择

Answer 1

1. 您应该在with()和withCount()方法中使用与whereHas()方法相同的条件。
2. 如果你有or条件在外面，我认为最好分割whereBetween()两个不同的语句equal or more和equal and less，因为$from和$to中的一个可能是空的。
3. 如果你的$from和$to变量包含日期，使用whereDate()方法更容易和安全。
4. 我认为将外部条件移动到回调中更清楚，然后您可以只使用一个User调用。

最终的结果应该是这样的:

$report = User
::withCount([
'orders' => function ($q) use ($from, $to) {
if (!empty($from)) {
$q->whereDate('created_at', '>=', $from);
}
if (!empty($to)) {
$q->whereDate('created_at', '<=', $to);
}
},
'shippings' => function ($q) use ($from, $to) {
if (!empty($from)) {
$q->whereDate('created_at', '>=', $from);
}
if (!empty($to)) {
$q->whereDate('created_at', '<=', $to);
}
}
])
->whereHas('shippings',function ($q) use ($from, $to) {
if (!empty($from)) {
$q->whereDate('created_at', '>=', $from);
}
if (!empty($to)) {
$q->whereDate('created_at', '<=', $to);
}
})
->orWhereHas('orders',function ($q) use($from, $to){
if (!empty($from)) {
$q->whereDate('created_at', '>=', $from);
}
if (!empty($to)) {
$q->whereDate('created_at', '<=', $to);
}
})
->orderByRaw('orders_count + shippings_count DESC')
->paginate(25);

或者只是为Shipping和Order模型添加范围:

public function scopeWhereDateBetween($builder, $from, $to)
{
if (!empty($from)) {
$builder->whereDate('created_at', '>=', $from);
}
if (!empty($to)) {
$builder->whereDate('created_at', '<=', $to);
}
}

结果应该是这样的:

$report = User
::withCount([
'orders' => function ($q) use ($from, $to) {
$q->whereDateBetween($from, $to);
},
'shippings' => function ($q) use ($from, $to) {
$q->whereDateBetween($from, $to);
}
])
->whereHas('shippings',function ($q) use ($from, $to) {
$q->whereDateBetween($from, $to);
})
->orWhereHas('orders',function ($q) use($from, $to){
$q->whereDateBetween($from, $to);
})
->orderByRaw('orders_count + shippings_count DESC')
->paginate(25);