Laravel Nested Query Builder Json Response

Question

I am trying to get a certain structure from a query builder which looks as:

{
  "status": "success",
  "data": {
    "id": 1,
    "email": "[email protected]",
    "birth_date": "1992-08-17",
    "gender": "M",
    "is_active": 1,
    "role": {
      "id": 1,
      "name": "Admin",
      "created_at": "2017-01-11 15:16:14",
      "updated_at": null
    }
  }
}

As you can see, i need for the relationship to be nested, in this case User to Roles.

I can get this structure using eager load with User::with('role').

I have this query but it returns everything in one column. Is there any way i can get this same structure using query builder? Is using eager load bad practice?

User::select('users.id', 'users.full_name')
    ->join('roles as r', 'r.id', '=', 'users.role_id')
    ->where('users.id', $user_id)
    ->get();

Thanks in advance.

Alexey Mezenin · Accepted Answer · 2017-01-11 16:39:48Z

2

I'd say eager loading is the best practice. It's readable and maintainable way to work with data, it returns conveniently structured data etc.

Of course, you can achieve the same result with Query Builder and even raw queries, but at the end of the day, you'll get an unmaintainable app.

So, I'd recommend you to stay with:

User::with('role')->get();

answered Jan 11, 2017 at 16:39

Alexey Mezenin

164k27 gold badges309 silver badges287 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

raphbibus · Accepted Answer · 2017-01-11 16:34:34Z

0

If you have a many-to-many relation set up, you can just add it to you queried models by calling the relation method.

In your model, you can define the relation:

public function roles() {
    return $this->belongsToMany('App\Role', 'role_user', 'user_id', 'role_id');
}

In your controller, you can access it like this:

$user = User::find(id);
$user->roles;
return response()->json($user);

Now your user model will contain all attached roles and you can output it in your desired way.

See Laravel Eloquent Docs

answered Jan 11, 2017 at 16:34

raphbibus

1

Comments

Eric Tucker · Accepted Answer · 2017-01-11 17:41:30Z

0

Eager loading would be best practice the "Laravel" way. Assuming you have a Role model and your User model has the relation:

public function role()
{
    return $this->belongsTo(Role::class);
}

You can get your desired response with:

$user = User::with('role')->find($user_id);

return response()->json(['status' => 'success', 'data' => $user]);

answered Jan 11, 2017 at 17:41

Eric Tucker

6,3851 gold badge28 silver badges39 bronze badges

Collectives™ on Stack Overflow

Laravel Nested Query Builder Json Response

3 Answers 3

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related