I have this situation, sorry if I post this in eloquent form but I think you will get the query anyway.
$x = user->waist_id;
$y = user->pants_size_id;
$z = [218,219];
$sizes = DB::table('pants_sizes')
->join('owned_item', 'pants_sizes.id', '=', 'owned_item.pants_size_id')
->join('owned_item_user', 'owned_item.id', '=', 'owned_item_user.owned_item_id')
->join('users', 'users.id', '=', 'owned_item_user.user_id')
->where('waist_id', $x)
->where('size_id', $y)
->whereIn('owned_item.model_id', $z)
->selectRaw("model_id, pants_sizes.id AS size_id")
->groupBy('model_id', 'pants_sizes.id')
->selectRaw("COUNT(pants_sizes.id) as Total")
->get();
return $sizes;
with this query I am joining some tables to get the sizes of a specific pants model owned by users based on same attributes (example waist and pants_size).
this returns the data in this form:
[
{
"model_id": 218,
"size_id": 4,
"Total": 3
},
{
"model_id": 218,
"size_id": 5,
"Total": 7
},
{
"model_id": 219,
"size_id": 4,
"Total": 3
},
{
"model_id": 219,
"size_id": 5,
"Total": 7
}
]
what I need is the data to be returned like this instead:
[
{
"model_id": 218,
"size_id": 4,
"Total_size_id_4": 3
"size_id": 5,
"Total_size_id_5": 7
"Total": 10
},
"model_id": 219,
"size_id": 4,
"Total_size_id_4": 3
"size_id": 5,
"Total_size_id_5": 7
"Total": 10
},
]
or in a way that I can use to produce statistics in the view, example:
[
{
"model_id": 218,
"4": "30%",
"5": "70%",
"Total": 10
},
"model_id": 219,
"4": "30%",
"5": "70%",
"Total": 10
},
]
"model 218 is owned by 10 users, 30% has size 4 and 70% has size 5"
Can this be done modifying the above query? with some group concat and sum stuff maybe?