count of orders and shipping with users table on specific period

Issue

in laravel app i want to make report to count the orders and shippings request for each user in specific period
i am created two inputs to and from to choose the dates
then i want to make query to get the users and count for orders and shippings count in table
how can i make it ?
in user model i have two relationship like it

public function orders()
{
    return $this->hasMany('App\Models\Order' , 'user_id');
}


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

in the order model

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

in the shipping model

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

i am trying to write many queries and this is the last one

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);
            }

can i any one help me ?

to get the orders and shippings for each user in specific period what i choose

Solution

  1. You should use the same condition in with() and withCount() methods like in the whereHas() methods.
  2. If you have or condition outside, I think it is better to split whereBetween() on two different statements equal or more and equal and less, because one of $from and $to could be empty.
  3. If your $from and $to variables contain dates, it is easier and safer to use whereDate() method.
  4. I think it is more clear to move your outside conditions inside callbacks, then you could use only one User call.

The final result should be like this:

$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);

Or just add scope to your Shipping and Order models:

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

And then result should look like this:

$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);

Answered By – IndianCoding

This Answer collected from stackoverflow, is licensed under cc by-sa 2.5 , cc by-sa 3.0 and cc by-sa 4.0

Leave a Reply

(*) Required, Your email will not be published