Question 275389 in Laravel Question posted in
The official Laravel Documentation can be found here.

Laravel group by if column value exits other wise return records

Laravel group by if column value exits then group by perform other wise as it is return records in single laravel query

i have tried this

$sql = $this->expense->builder()->where('session_year_id',1)->whereNull('staff_id')->orWhereNotNull(function($q){
            $q->groupBy('month');
        })->get();
Tags:

2

Answers


  1. 0

    It seems like you want to perform a conditional GROUP BY in Laravel based on whether a certain column value exists or not. The query you’ve posted has some issues, but I’ll help you correct it. You can achieve this using conditional logic and the groupBy method.

    Assuming you want to group by the ‘month’ column if there are any non-null values in that column, and if all values are null, you want to return all records without grouping, you can do the following:

    $query = $this->expense->builder()->where('session_year_id', 1)->whereNull('staff_id');

if (!$this->expense->whereNotNull('month')->exists()) {
    $results = $query->get();
} else {
    $results = $query->groupBy('month')->get();
}

    In this code:

    1. First, you filter your initial query to get records where ‘session_year_id’ is 1 and ‘staff_id’ is null.
    2. Next, you check if there are any records with non-null values in the ‘month’ column using whereNotNull(‘month’)->exists(). If there are none, you return all records without grouping.
    3. If there are non-null values in the ‘month’ column, you group the results by ‘month’ and get the grouped data.
      This way, the grouping will only occur when there are non-null values in the ‘month’ column, and you get all records as they are when all values in ‘month’ are null.
    Login or Signup to reply.
  2. 0

    To achieve the desired result of returning both grouped by month and separate records when there are null values in the ‘month’ column, you can use a combination of GROUP BY and a union query. Here’s how you can do it in Laravel:

    $groupByMonth = $this->expense->builder()
    ->where('session_year_id', 1)
    ->whereNotNull('month')
    ->groupBy('month')
    ->get();

$noGroupByMonth = $this->expense->builder()
    ->where('session_year_id', 1)
    ->whereNull('staff_id')
    ->whereNull('month')
    ->get();

$results = $groupByMonth->union($noGroupByMonth);

    In this code:

    1. The first query $groupByMonth selects and groups records with non-null values in the ‘month’ column.
    2. The second query $noGroupByMonth selects records where ‘session_year_id’ is 1, ‘staff_id’ is null, and ‘month’ is null.
    3. The union method is used to combine the two query results into a single result set.
      This approach will give you both grouped records based on ‘month’ and separate records when ‘month’ is null.
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search