Laravel 5离开了加入关系

Question

I have two database tables: users and students. A student is a user, but has extra data (address). I have a belongsTo('User') relationship in my Student model.

If I now query the Student model like this:

$this->student
    ->with('user')
    ->first();

I get this result:

{
    "id": 1,
    "user_id": 12,
    "street": "Petershof",
    "house_number": "3787",
    "postal_code": "8161 NM",
    "city": "Tienhoven",
    "user": {
        "id": 12,
        "email": "[e-mail]",
        "first_name": "Nathan",
        "last_name": "van Dijk",
        "name": "Nathan van Dijk"
    }
}

However, I want to flatten the result, so that the user fields are in the parent (Student) object. Like this:

{
    "id": 1,
    "user_id": 12,
    "street": "Petershof",
    "house_number": "3787",
    "postal_code": "8161 NM",
    "city": "Tienhoven",
    "email": "[e-mail]",  // = user field
    "first_name": "Nathan",        // = user field
    "last_name": "van Dijk",       // = user field
    "name": "Nathan van Dijk"      // = user field
}

I have tried to use leftJoin('users', 'user_id', '=', 'users.id') instead of with(), but then I lose my virtual attribute 'name' (defined in the User model) and also can't query relations of User anymore.

How can I achieve this in a clean way?

Answer 1

$this->student
  ->leftJoin('users', 'users.id', '=', 'students.user_id')
  ->select(
    'students.*',
    'users.email',
    'users.first_name',
    'users.last_name',
    DB::raw("concat(users.first_name, ' ', users.last_name) as name"),
  )->first();

You could drop the table in select like users.email in this case.

The relations will work just the same, I suppose the problem was only with overriding students id with users id, if you didn't specify the select and just grabbed all the fields.