How to get rewards for each user in laravel?-CodePudding

I'm having 3 tables. users, rewards, and gained rewards. I want to get the rewards for each colleague of the user.

I wrote the following api, but it didn't work.

    public function getColleaguesGainedRewards(Request $request){
        $company_id = $request->company_id;
        $colleagues = GainedReward::find(1)->users->where('company_id', $company_id);
        $rewards = GainedReward::all();

        return response()->json([
            "status" => "success",
            $colleagues => $rewards,
        ], 200);
    }

And I assigned the following relationship in the gained reward model:

    public function users()
    {
        return $this->hasMany(User::class, 'company_id');
    }
    public function rewards()
    {
        return $this->hasMany(Reward::class, 'id');
    }

The users table looks as follow:

id	name	company_id
1	John	1
2	Jack	1
3	Alex	2

The companies table looks as follow:

id	name
1	Abc
2	Def

The rewards table looks as follow:

id	name
1	Day Off
2	Coffee

The gained_rewards table looks as follow:

id	reward_id	user_id
1	1	1
2	2	1
3	2	2

So I want to get all the rewards for each colleague. For example the result should look as follows:

Get the rewards for each employee at company "Abc":

John	Jack
Day Off	Day Off
	Coffee

CodePudding user response：

From what i understood you want to get the users by company ID and for each user you want the gained_rewards and rewards

You should add the relationship for gained_rewards inside the users model

public function gainedRewards()
{
    return $this->hasMany(GainedRewards::class, ‘id’,'user_id');
}

You can also add the rewards relationship inside of the GainedRewards model

public function rewards()
{
    return $this->hasMany(Reward::class, 'id', 'reward_id');
}

Then when you do a

    $user = User::where('company_id', $request->company_id)->with([
        'gainedRewards' => function($query){},
        'gainedRewards.rewards' => function($query) {}
     ])->get();

you can do loop through each user and get the gainedRewards for that user and the rewards

CodePudding user response：

Grouping per user_id and storing each reward_id into an associative array is one solution which I have implemented recently

$groupedRewards = array();

$gainedRewards = \App\GainedReward::all();

foreach ($gainedRewards as $gainedReward) {
  $obj = array('reward_id' => $gainedReward->reward_id);

  if (array_search($gainedReward->user_id, array_column($groupedRewards, 'user_id')) !== false) {
    $the_key = array_search($gainedReward->user_id, array_column($groupedRewards, 'user_id'));
    $rewards = $groupedRewards[$the_key]['rewards'];

    array_push($rewards, $obj);

    $groupedRewards[$the_key]['rewards'] = $rewards;
  }
  else {
    array_push($groupedRewards, array('user_id' => gainedReward->user_id, 'rewards' => array($obj)));
  }
}

echo '<pre>' , var_dump($groupedRewards) , '</pre>';

Then you can parse any data you need

for ($i = 0; $i < count($groupedRewards); $i  ) {
  echo '<pre>' , var_dump($groupedRewards[$i]->user_id) , '</pre>';
  for ($j = 0; $j < count(groupedRewards[$i]->rewards); $j  ) {
    ...
  }
}