$regu = DB::table('patrol_transactions as a')
    ->leftJoin('patrol_users as b', 'a.patrol_user_id', 'b.id')
    ->where('client_location_id', auth::user()->client_location_id)
    ->whereBetween('a.created_at', [Carbon::now()->startOfWeek(), Carbon::now()->endOfWeek()])
    ->select('patrol_user_id', DB::raw('count(*) as total'), 'b.name as name')
    ->groupBy('patrol_user_id', 'name')
    ->get();

PatrolTansaction.php

public function patrolUser()
{
    return $this->belongsTo(PatrolUser::class, 'patrol_user_id');
}

I have a query builder code like the one above, what would it look like if it was changed to eloquent?

CodePudding user response：

There isn't much more to win.

To convert the Laravel query builder code you provided to Eloquent ORM syntax, you can use the PatrolTransaction model and the PatrolUser model to define the relationships between the patrol_transactions and patrol_users tables.

$regu = PatrolTransaction::leftJoin('patrol_users as b', 'patrol_transactions.patrol_user_id', 'b.id')
    ->where('client_location_id', auth::user()->client_location_id)
    ->whereBetween('patrol_transactions.created_at', [Carbon::now()->startOfWeek(), Carbon::now()->endOfWeek()])
    ->select('patrol_user_id', DB::raw('count(*) as total'), 'b.name as name')
    ->groupBy('patrol_user_id', 'name')
    ->get();

I didn't tried that though, so let me know if that answers to your question.

CodePudding user response：

As per your code, you already defined the relationship patrolUser(), so there is no need to use JOIN to merge it again. Use with() to load the relationship.

$regu = PatrolTransaction::where('client_location_id', auth::user()->client_location_id)
    ->whereBetween('created_at', [Carbon::now()->startOfWeek(), Carbon::now()->endOfWeek()])
    ->with(['patrolUser:id,name'])
    ->select('patrol_user_id', DB::raw('count(*) as total'))
    ->groupBy('patrol_user_id', 'name')
    ->get();