Laravel 5.1 loading relationship data into selected row

Question

I have a timesheet table and a user table in my database. The following relationship is setup on the timesheet model.

/**
 * The user that owns the timesheet.
 *
 * @return Object
 */
public function user()
{
    return $this->belongsTo('App\Models\User\User');
}

The above means I can get user data when I select timesheets from the database by using something like:

$this->timesheet->whereStatus('Approved')->with('user');

This will load the user object in the result and if converted to an array will look like so;

0 => array:13 [▼
    "id" => 1
    "user_id" => 2
    "week" => 1
    "year" => 2016
    "week_ending" => "Sunday 10th January 2016"
    "total_hours" => "45.00"
    "token" => "0e6796a2dc68066c8d36ff828c519af00657db02b733309b8a4ac0f7b5d6a385"
    "status" => "Approved"
    "supervisor_id" => 1
    "approved_by" => 1
    "created_at" => "2016-01-13 15:42:49"
    "updated_at" => "2016-01-14 14:52:07"
    "user" => array:7 [▼
      "id" => 2
      "first_name" => "Bill"
      "last_name" => "Andrews"
      "email" => "[email protected]"
      "status" => 1
      "created_at" => "2016-01-13 15:38:18"
      "updated_at" => "2016-01-14 14:50:03"
    ]
  ]

However, I only need first_name and last_name from the user table. Is there a way to merge/flatten the user array with the timesheet so that it looks like this instead;

0 => array:14 [▼
    "id" => 1
    "user_id" => 2
    "week" => 1
    "year" => 2016
    "week_ending" => "Sunday 10th January 2016"
    "total_hours" => "45.00"
    "token" => "0e6796a2dc68066c8d36ff828c519af00657db02b733309b8a4ac0f7b5d6a385"
    "status" => "Approved"
    "supervisor_id" => 1
    "approved_by" => 1
    "created_at" => "2016-01-13 15:42:49"
    "updated_at" => "2016-01-14 14:52:07"
    "first_name" => "Bill"
    "last_name" => "Andrews"
  ]

I have tried to use eager loading like so;

$this->timesheet->with(['user' => function ($query) {
    $query->select('first_name', 'last_name');
}])->get()->toArray();

However, it results in the following output;

array:126 [▼
  0 => array:13 [▼
    "id" => 1
    "user_id" => 2
    "week" => 1
    "year" => 2016
    "week_ending" => "Sunday 10th January 2016"
    "total_hours" => "45.00"
    "token" => "0e6796a2dc68066c8d36ff828c519af00657db02b733309b8a4ac0f7b5d6a385"
    "status" => "Approved"
    "supervisor_id" => 1
    "approved_by" => 1
    "created_at" => "2016-01-13 15:42:49"
    "updated_at" => "2016-01-14 14:52:07"
    "user" => null
  ]

Answer 1

The reason why the user relationship is null in your second example is because in order for Eloquent relationships to work, it needs the keys that tie the relationships. In other words...

1. Get timesheet.
2. Get user with only first_name and last_name.
3. Build relationship.
4. Since you did not fetch the user's id, the user's id and the timesheet's user_id do not match so the relationship cannot be built.

In order for your query to work, you need to adjust it like this:

$this->timesheet->with(['user' => function ($query) {
    $query->select('id', 'first_name', 'last_name');
}])->get()->toArray();

With that out of the way, if you want a flattened result, I think it's best to use joins rather than eager loading because of the nature of eager loading.

$this->timesheet
     ->join('users', 'timesheets.user_id', '=', 'users.id')
     ->select('timesheets.*', 'users.first_name', 'users.last_name')
     ->get()->toArray();

This assumes that your table names are users and timesheets.

Answer 2

Have you tried to get a list

->lists('first_name', 'last_name');

or if you wanted to perform a select

->select('first_name', 'last_name')->get()

Update You can also perform eager loading to eager load related objects. Here is an example

$users = App\User::with(['posts' => function ($query) {
    $query->select('first_name', 'last_name');

}])->get();

Please let me know if that helps.

Answer 3

Laravel models have a way to modify data before getting/setting an attribute. You can actually add the attributes to the model by defining a getter function. This will let you reference the user names in the same way you would the user_id or status. These functions are also great for changing date formats for a view or sanitizing form input.

/**
 * Get the first name of the user.
 *
 * @return string
 */
public function getFirstNameAttribute()
{
    return $this->user->first_name;
}

/**
 * Get the last name of the user.
 *
 * @return string
 */
public function getLastNameAttribute()
{
    return $this->user->last_name;
}

Answer 4

That's exactly what a join do.

From the documentation

The query builder may also be used to write join statements. To perform a basic SQL "inner join", you may use the join method on a query builder instance. The first argument passed to the join method is the name of the table you need to join to, while the remaining arguments specify the column constraints for the join.

$this->timesheet
      ->leftjoin('user', 'user.id', '=','timesheet.user_id')
      ->get()
      ->toArray();

If you want to be more selective on your fields, you can choose what you select;

$this->timesheet
      ->leftjoin('user', 'user.id', '=','timesheet.user_id')
      ->select('timesheet.*', 'user.first_name', 'user.last_name')
      ->get()
      ->toArray();

As other have suggested, it might be better to use the DB query builder such as

this->timesheet = DB::table('timesheet')
                 ->where('timesheet.status', 'Approved')
                 ->leftjoin('user', 'user.id', '=','timesheet.user_id')
                 ->select('timesheet.*', 'user.first_name', 'user.last_name')
                 ->get()
                 ->toArray();