CODEWITHSUNDEEP
laravellaravel-5
Naveen Kumar
Naveen Kumar

Reputation: 1626

Fetching a column data of another table using id Laravel

I have a table which has a column interviewer_id which is equal to the id of users table. I am grouping the data according to the interviewer_id in my controller and want to fetch the name belongs to the id of each group from the users table. Here is what i tried 

$int_payment = IntPayments::where('month',$month_year)->groupBy('interviewer_id')->get();
    foreach ($int_payment as $inter) {
        $inters = User::where('id',$inter->interviewer_id)->get();

    }
    return response()->json(['inters',$inters]);

Here my response shows only one group data it fetched through User. I want get the row from the users table where its id is group's ID

Here is what i exactly want. When i return $int_payment in response i get this response.

["inters",[{"id":2,"candidate_id":"13","interviewer_id":"1","profile_id":"","exp":"5-10","payment_status":"unpaid","month":"Feb 2017","created_at":"2017-02-08 04:56:40","updated_at":"2017-02-08 04:56:40"},{"id":4,"candidate_id":"13","interviewer_id":"2\r\n","profile_id":"","exp":"5-10","payment_status":"unpaid","month":"Feb 2017","created_at":"2017-02-08 04:56:40","updated_at":"2017-02-08 04:56:40"},{"id":1,"candidate_id":"13","interviewer_id":"4","profile_id":"","exp":"5-10","payment_status":"unpaid","month":"Feb 2017","created_at":"2017-02-08 04:56:40","updated_at":"2017-02-08 04:56:40"}]]

If you see the response there are three groups with ids 2,4 and 1. These ids are id of the user in the user table. I want to get the user's name which belongs to the ids.

Upvotes: 1

Views: 4743

Answers (3)

omitobi
omitobi

Reputation: 7334

Simply use Laravel model relationship to make life easier:

Make you User Model have:

public function intpayments(){
    $this->hasMany(IntPayments::class, 'interviewer_id');
}

Then your IntPayments model have:

public function user(){
    $this->belongsTo(User::class, 'interviewer_id');
}

So you can easily retrieve the same result you want with:

$inters = IntPayments::where('month',$month_year)->load('user');

Then you can loop through this result to get the fields you need say:

$result = [];
    foreach($inters as $inter)
    {
       $result[] = $inter->user->name;
    }
return $result;

UPDATED:

But i want only the name column from that table and the data from IntPayments is my first priority

Okay so you can still use the same result returned from the query:

$result = [];
    foreach($inters as $k => $inter)
    {
       $result[$k]['name'] = $inter->user->name; //this is the user's name
       $result[$k]['id'] = $inter->id; //this is intPayments's id
       $result[$k]['interviewer_id'] = $inter->interviewer_id; //this is intPayments interviewer's 'id
       ......
    }

Maybe you don't know, the result from the $inters variable would give you first, each record from IntPayments then with the User for each of them.

This means that inters will be naturally like (if I send the response as json):

[
  {
    "id": 1,
    "candidate_id": "1",
    "interviewer_id": "3"
    "user": {
      "id": 1,
      "name": "second last",
      "email": "[email protected]",
    }
  },
  {
    "id": 2,
    "candidate_id": "1",
    "interviewer_id": "5",
    "user": {
    "id": 1,
      "name": "first last",
      "email": "[email protected]",
    }
  },
  .....
]

This means you can use this response as you wish and access the fields you want.

Hope this helps :)

Upvotes: 0

Sanzeeb Aryal
Sanzeeb Aryal

Reputation: 3266

Try returning $inters in array:

foreach ($int_payment as $inter) {
        $inters[] = User::where('id',$inter->interviewer_id)->get();
    }
return $inters;

However, setting up relationship between User and IntPayment models will be much easier to handle this kind of situation. See in doc Eloquent relationship.

Upvotes: 3

Pedram marandi
Pedram marandi

Reputation: 1614

Try this one: 

IntPayments::select('interviewr_TABLE.*', 'users.name')->where('month',$month_year)->leftJoin('users', 'users.id', '=', 'interviewr_TABLE') ->groupBy('interviewer_id')->get()

The only problem here is I don't know exactly what is your IntPayments table name is.

Upvotes: 0

Related Questions