如何按两列对查询进行计数和分组？

Question

I have an collection returned by a Eloquent Query. Each element of the collection is an array with only two integers elements (branch_id, event_id). I need to make and array or collection where all the identical pairs of elements are combined in the same array, because I need to count how many times a pair of elements appears and also being able to access to its values.

I am using the groupBy method of Query Builder to group the result by two columns, but it only returns the data combined by the first column name I passed to the method.

For Example:

    array:15 [▼
  0 => {#277 ▼
    +"branch_id": 1
    +"event_id": 1
  }
  1 => {#279 ▼
    +"branch_id": 1
    +"event_id": 1
  }
  2 => {#280 ▼
    +"branch_id": 1
    +"event_id": 1
  }
  3 => {#281 ▼
    +"branch_id": 1
    +"event_id": 1
  }
  4 => {#282 ▼
    +"branch_id": 1
    +"event_id": 2
  }
  5 => {#283 ▼
    +"branch_id": 1
    +"event_id": 21
  }
  6 => {#292 ▼
    +"branch_id": 58
    +"event_id": 21
  }
  7 => {#284 ▼
    +"branch_id": 2
    +"event_id": 5
  }
  8 => {#285 ▼
    +"branch_id": 3
    +"event_id": 3
  }
  9 => {#286 ▼
    +"branch_id": 4
    +"event_id": 4
  }
  10 => {#287 ▼
    +"branch_id": 5
    +"event_id": 9
  }
  11 => {#289 ▼
    +"branch_id": 5
    +"event_id": 9
  }
  12 => {#288 ▼
    +"branch_id": 5
    +"event_id": 8
  }
  13 => {#290 ▼
    +"branch_id": 7
    +"event_id": 10

As you can see, the first four arrays has the same values (branch_id: 1, event_id: id) I need to retrieve those values combined in a single array, and so forth with every array that has the same values.

Answer 1

I was able to do it this way...

    public function getAllIncidents()
{
    $i = 0;
    $data = [];
    $values = DB::table('incidents')->select('branch_id', 'event_id')->orderBy('branch_id')->get()->groupBy('event_id');

    foreach ($values as $key => $value) {
        $collections[$i] = $value->groupBy('branch_id');
        $i++;
    }

    $collections = array_collapse($collections);
    $i = 0;

    foreach ($collections as $key => $collection) {
        $array                  = $collection->toArray();
        $data[$i]['count']      = sizeof($array);
        $data[$i]['branch_id']  = $array[0]->branch_id;
        $data[$i]['event_id']   = $array[0]->event_id;
        $i++;            
    }

    return response()->json($data, 200);
}