Laravel : How create new array from two arrays with deferent length-CodePudding

I have two array with deferent length created in controller like this :

$absences = Absence::query()
            ->select('name')
            ->selectRaw('count(name) as counts')
            ->whereBetween('date', [$req->from, $req->to])
            ->where('stage',$req->stage)
            ->where('group',$req->group)
            ->where('subject',$req->subject)
            ->where('vacs','No')
            ->groupBy('name')
            ->get();
$vacs = Absence::query()
            ->select('name')
            ->selectRaw('count(name) as counts')
            ->whereBetween('date', [$req->from, $req->to])
            ->where('stage',$req->stage)
            ->where('group',$req->group)
            ->where('subject',$req->subject)
            ->where('vacs','yes')
            ->groupBy('name')
            ->get();

depend on this I will get two arrays the outputs will be something like this :

absence output

[
  {
    "name": "Sam",
    "counts": 2
  },
  {
    "name": "Tom",
    "counts": 1
  }
]

vacs output

[
  {
    "name": "Sam",
    "counts": 1
  }
]

Now I want to create new array with this output :

[
  {
    "name": "Sam",
    "abs_counts": 2,
    "vac_counts": 1,
  },
  {
    "name": "Tom",
    "abs_counts": 1,
    "vac_counts": 0,
  }
]

CodePudding user response：

You can do it in one query:

$data = Absence::query()
    ->select('name')
    ->selectRaw("name, sum(if(vacs = 'No', 1, 0)) as abs_counts, sum(if(vacs = 'yes', 1, 0)) as vac_counts")
    ->whereBetween('date', [$req->from, $req->to])
    ->where('stage', $req->stage)
    ->where('group', $req->group)
    ->where('subject', $req->subject)
    ->groupBy('name')
    ->get();