Laravel Nested Query Builder Json Response

Asked at 2017-01-11 16:20:26Z
  • 5 Subscribers

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.full_name')
    ->join('roles as r', '', '=', 'users.role_id')
    ->where('', $user_id)

Thanks in advance.

3 answers in total

Alexey Mezenin Posted at 2017-01-11 16:39:48Z

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:

raphbibus Posted at 2017-01-11 16:34:34Z

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);
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

Eric Tucker Posted at 2017-01-11 17:41:30Z

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]);

Answer this questsion