CODEWITHSUNDEEP
phplaravel
Jan-Paul Kleemans
Jan-Paul Kleemans

Reputation: 828

Laravel 5 leftJoin on belongsTo relation

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": "[email protected]",
        "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": "[email protected]",  // = 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?

Upvotes: 5

Views: 10671

Answers (2)

Lpgfmk
Lpgfmk

Reputation: 401

Old question, but here goes.

Reading your comment it seems you want to make an api for this. If it's only one endpoint formatting the result on the fly shouldn't be an issue however for more endpoints I would suggest a transformer pattern.

This would work by having a separate class return an array based on your db data. Then you take that array and use it in your response().

So, in your controller, instead of doing something like return response(Student::all()); you could do something like return response(StudentTransformer::transform(Student::all())).

The advantage here is you have only one folder (your Transformers) to check for response data format. The disadvantage is when your DB changes you also need to update your transformers.

Upvotes: 0

Jarek Tkaczyk
Jarek Tkaczyk

Reputation: 81187

$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.

Upvotes: 0

Related Questions