How to return JSON response from a query with eloquent?

Question

I am working on this query: (I wish I could pass this same query using eloquent and return a JSON as response.)

public function getChart(Request $request)
    {      
        $_orders = DB::table('users')
            ->join('orders','orders.user_id','=','users.id')
            ->join('model_has_roles', 'users.id', '=', 'model_has_roles.model_id')                        
            ->select('users.id','users.name', DB::raw('COUNT(orders.id) as orders_by_user'), 'model_has_roles.role_id as rol')                   
            ->where('model_has_roles.role_id', '2');
        $_orders->groupBy('orders.user_id', 'users.id',  'users.name',  'model_has_roles.role_id');
        $orders=$_orders->get();

        return ['orders' => $orders];

    }

This query is already resolved and returns me as a result: the name of the operator who was assigned a work order and the number of work orders in finished state that that operator has. for this, relate the users table and the orders table.

{
"orders": [
{
"id": 4,
"name": "Luis",
"orders_by_user": 2,
"rol": 2
},
{
"id": 6,
"name": "Jose",
"orders_by_user": 1,
"rol": 2
},
{
"id": 7,
"name": "Miguel",
"orders_by_user": 1,
"rol": 2
}
]
}

If you look at my query response, my array is called orders inside orders I get what I need:

"orders": [
{
"id": 4,
"name": "Luis",
"orders_by_user": 2,
"rol": 2
},

As I mentioned earlier, I wish I could pass this same query using eloquent and return a JSON in response.

How can I customize this JSON response? It would be something like this: separate name in one array and orders_by_user in another array

example of what I want to achieve

"users": [
{

"name": "Luis"
}
]
"orders": [
{

"orders_by_user": 2

}
]

I need your help

user_629673 · Answer

you can return json response like this

return response()->json(['name' => 'Abigail', 'state' => 'CA'])

read documentation here

https://laravel.com/docs/7.x/responses#json-responses

Note explaining relations to @Rodrigo Ruiz

in user model has your relation like this

class User extends Model
{
    public function Orders()
    {
        return $this->hasMany(App\Order::class);
    }
}

Order Model

class Order extends Model
{
    public function user()
    {
        return $this->belongsTo(App\User::class);
    }
}

$user = User::first();

get all orders of user

$orders = $user->orders()->get();

get first order

$order = $user->orders()->first();

if you call relation each time it will execute a query on each call.

Optimize call of relations it will load all relations just in 1 query.

$user = User::with('orders')->get();

$order = $user->orders->first();

user_5803838 · Answer

List item

@umefarooq is right, I always follow his steps and sometimes if I don't want it to return all the values of that particular model, I return the result through Laravel API Resource. This gives you the flexibility to determine which fields you will like to return from the eloquent query.

In your controller, rather than use it like

return response()->json($order);

I use it like this

 return OrderResource::collection($orders);

You can customize this query to any style you want as long as you know how to pass all required parameters into the collection. So if you want to fetch just the basic information about a user and order:

class OrderResource extends JsonResource
return [
           'user'=>[
                'id' => $this->id,
                'name' => $this->name,
                  ],
            'orders'=>[
                'order_id' => $this->order->order_id,
                'order_rol' =>  $this->rol
            ]
        ];

How to return JSON response from a query with eloquent?

2 Answers