Laravel Eloquent multiple sums-CodePudding

Columns of contact table

Name=client_id
Name=room_id
Name=stats - a number
Name=date

I need to calculate stats for a year, per weekly basis, monthly, and quaterly, many sums of the stats column. I made this SQL for the older system, now I need to get the sum the stats column, not count them like before.

SELECT 
  SUM(
    date > '2021-01-11 00:00:00' 
    AND date < '2021-01-18 00:00:00' 
    AND room_id = 6 
    AND client_id = 1
  ) as week1,
  SUM(
    date > '2021-12-20 00:00:00' 
    AND date < '2021-12-27 00:00:00' 
    AND room_id = 6 
    AND client_id = 1
  ) as week51, 
  SUM(
    date > '2021-01-01 00:00:00' 
    AND date < '2021-01-31 23:59:00' 
    AND room_id = 6 
    AND client_id = 1
  ) as month1, 
  SUM(
    date > '2021-12-01 00:00:00' 
    AND date < '2021-12-31 23:59:00' 
    AND room_id = 6 
    AND client_id = 1
  ) as month12 
FROM 
  contact;

Is is possible to do it with Eloquent, or I need to use DB facade? If it's an easier way to do it with Eloquent, i'd like to use it.

CodePudding user response：

you can do something like this

$conditions  = [
  
    'week1' =>  [
    
        ['date' ,  '>' ,  '2021-01-11 00:00:00' ] ,     
        ['date' ,  '<' ,  '2021-01-18 00:00:00' ] ,     
        ['room_id' ,  6 ] ,     
        ['client_id' ,  1 ] ,     

    ] , 
    'month1' =>  [
    
        ['date' ,  '>' ,  '2021-12-01 00:00:00' ] ,     
        ['date' ,  '<' ,  '2021-01-31 23:59:00' ] ,     
        ['room_id' ,  6 ] ,     
        ['client_id' ,  1 ] ,     

    ] , 
  
  ];

  $output = [];
  foreach ($conditions as $k=>$condition )
      $output[$k] = Contact::where($condition)->sum('stats');

---------------- using union -----------------

$conditions  = [
  
    'week1' =>  [
    
        ['date' ,  '>' ,  '2021-01-11 00:00:00' ] ,     
        ['date' ,  '<' ,  '2021-01-18 00:00:00' ] ,     
        ['room_id' ,  6 ] ,     
        ['client_id' ,  1 ] ,     

    ] , 
    'month1' =>  [
    
        ['date' ,  '>' ,  '2021-12-01 00:00:00' ] ,     
        ['date' ,  '<' ,  '2021-01-31 23:59:00' ] ,     
        ['room_id' ,  6 ] ,     
        ['client_id' ,  1 ] ,     

    ] , 
  
];

$base_query = false ;
foreach ($conditions as $k=>$condition )
{
    $query = Contact::selectRaw("SUM(stats) as total , '$k' as 'title'   ")->where($condition) ;
    if($base_query === false )
        $base_query = $query ;
    else
        $base_query->union($query);
}

$results = $base_query->get() ;

   foreach ($results as $result )
   {
       dump($result->title , $result->total );
   }

CodePudding user response：

You can simply add the subqueries using the addSelect() method, and have Carbon deal with the dates:

$room = 6;
$client = 1;
$results = Contact::addSelect([
        'week1' => Contact::selectRaw('SUM(stats)')->where('room_id', $room)->where('client_id', $client)->whereBetween('date', [now()->week(1)->startOfWeek(), now()->week(1)->endOfWeek()]),
        'week51' => Contact::selectRaw('SUM(stats)')->where('room_id', $room)->where('client_id', $client)->whereBetween('date', [now()->week(51)->startOfWeek(), now()->week(51)->endOfWeek()]),
        'month1' => Contact::selectRaw('SUM(stats)')->where('room_id', $room)->where('client_id', $client)->whereBetween('date', [now()->month(1)->startOfMonth(), now()->week(1)->endOfMonth()]),
        'month12' => Contact::selectRaw('SUM(stats)')->where('room_id', $room)->where('client_id', $client)->whereBetween('date', [now()->month(12)->startOfMonth(), now()->month(12)->endOfMonth()]),
    ])
    ->where('room_id', $room)
    ->where('client_id', $client)
    ->get()