Be part of JetBrains PHPverse 2026 on June 9 – a free online event bringing PHP devs worldwide together.
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
Hi.
If you want the users and orders_by_users in two separate arrays you can do something like this:
$users = $orders->pluck('name');
$orders = $orders->pluck('orders_by_user');
return compact('users', 'orders');
You will get this result:
{
"users": ["Luis", "Jose" ....]
"orders": [2, 3, 5 ....]
}
This is what you wanted?
if that would be the end result but would you help me pass my query with eloquent
What do you mean by that? Do you mean you would like to use a Model for that?
In your code you use the Eloquent Query Builder to build your query which is totally fine since it's a complex query.
If ajax
return Response::json($data);
@zsoltgyure if it is a complex query, then you say that I only added what you have indicated and with that I solve my question? please confirm
Yes.
@zsoltgyure Thanks for your help, it is the result I was looking for, however I still think that this same query with fewer lines of code could be done using eloquent since I have all my related models.
Can you export the involved tables to an SQL file and share it with me? I can check it then.
@zsoltgyure dbtecind Great thanks for your time relating the users table and the orders table
Ok so I don't know how are the roles structured in the database, but I created a simple model for that. You may need to change that part a bit.
This code gave me the exact same results.
$users = collect();
$orders = collect();
$records = ModelHasRole::with(['user' => function($query){
return $query->withCount('orders');
}])
->whereRoleId(2)
->get();
$records->each(function($record) use($users, $orders) {
$users->push($record->user->name);
$orders->push($record->user->orders_count);
});
return compact('users', 'orders');
My helper ModerHasRole.php
<?php
namespace App;
use Illuminate\Database\Eloquent\Model;
class ModelHasRole extends Model
{
public function user()
{
return $this->belongsTo(User::class, 'model_id');
}
}
So again - if you don't have this model just tweak the code a bit.
@zsoltgyure I thank you for your contribution to answer my question. Modify your query to this:
$users = collect();
$orders = collect();
$records = Order::with(['user' => function($query){
return $query->withCount('orders');
}])
->get();
$records->each(function($record) use($users, $orders) {
$users->push($record->user->name);
$orders->push($record->user->orders_count);
});
return compact('users', 'orders');
As a result I get this:
{
"users": [
"Luis",
"Luis",
"Jose",
"Miguel"
],
"orders": [
2,
2,
1,
1
]
}
You may notice that the operator name Luis is repeated 2 times, as well as his completed work orders.
Why does this happen?. How could I avoid repeating the same name of the operator Luis and his number of completed orders? How can I solve it?
@zsoltgyure I was thinking about the query you gave me as an answer, I modified your answer adapting it to mine, could you review it? Observe the json response that I receive, I am doubling the operator Luis with his number of orders
You can also add groupBy() before the get() and you can group your results.
Please or to participate in this conversation.