MySQL Forums
Forum List  »  General

eloquent mysql: aggregate data returned by query with sums (group concat?)
Posted by: Christian Nuvoli
Date: March 23, 2016 02:57PM

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?

Options: ReplyQuote




Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.