Return subset of the same data

Posted 2 years ago by thiagocardoso

Hi!

I have a query, but it isn't doing quite what I need. First my table (a small part of the table):

name  | user_id | date
--------------------------------------
DELTA | 28      | 2017-04-03 09:00:00
DELTA | 28      | 2017-04-07 09:00:00
DELTA | 28      | 2017-04-11 09:00:00
DELTA | 28      | 2017-04-15 09:00:00
DELTA | 28      | 2017-04-19 09:00:00
DELTA | 28      | 2017-04-23 09:00:00
DELTA | 28      | 2017-04-27 09:00:00
BETA  | 3       | 2017-04-21 09:00:00
BETA  | 3       | 2017-04-09 09:00:00
BETA  | 3       | 2017-04-05 09:00:00
BETA  | 3       | 2017-04-25 09:00:00
BETA  | 3       | 2017-04-29 09:00:00
BETA  | 3       | 2017-04-01 09:00:00
BETA  | 3       | 2017-04-13 09:00:00
BETA  | 3       | 2017-04-17 09:00:00
DELTA | 33      | 2017-04-27 09:00:00
DELTA | 33      | 2017-04-03 09:00:00
DELTA | 33      | 2017-04-07 09:00:00
DELTA | 33      | 2017-04-19 09:00:00
DELTA | 33      | 2017-04-23 09:00:00
DELTA | 33      | 2017-04-11 09:00:00
DELTA | 33      | 2017-04-15 09:00:00

And here's my Eloquent query:

        $q = Escala::query();
        $q = $q->join('users as user', 'user.id', '=', 'user_id')
                    ->select(['escalas.name', 'user_id'])
                    ->selectRaw('data_inicio as escalas')
                    ->orderBy('user.name', 'asc');

And it's returning a JSON for my application:

[
  {
    "name": "DELTA",
    "user_id": 28,
    "escalas": "2017-04-27 09:00:00"
  },
  {
    "name": "DELTA",
    "user_id": 28,
    "escalas": "2017-04-03 09:00:00"
  },
  {
    "name": "DELTA",
    "user_id": 28,
    "escalas": "2017-04-07 09:00:00"
  },
  {
    "name": "DELTA",
    "user_id": 28,
    "escalas": "2017-04-19 09:00:00"
  },
  {
    "name": "DELTA",
    "user_id": 28,
    "escalas": "2017-04-23 09:00:00"
  },
  {
    "name": "DELTA",
    "user_id": 28,
    "escalas": "2017-04-11 09:00:00"
  },
  {
    "name": "DELTA",
    "user_id": 28,
    "escalas": "2017-04-15 09:00:00"
  },
  {
    "name": "BETA",
    "user_id": 3,
    "escalas": "2017-04-21 09:00:00"
  },
  {
    "name": "BETA",
    "user_id": 3,
    "escalas": "2017-04-09 09:00:00"
  },
  {
    "name": "BETA",
    "user_id": 3,
    "escalas": "2017-04-05 09:00:00"
  },
  {
    "name": "BETA",
    "user_id": 3,
    "escalas": "2017-04-25 09:00:00"
  }, ... ,
  {
    "name": "DELTA",
    "user_id": 11,
    "escalas": "2017-04-19 09:00:00"
  }
]

As you can see, it's returning on separate lines what I need to be together, as the following example (handmade, just to be clear):

[
  {
    "name": "DELTA",
    "user_id": 28,
    "escalas": {
      1: "2017-04-27 09:00:00",
      2: "2017-04-03 09:00:00",
      3: "2017-04-07 09:00:00",
      4: "2017-04-19 09:00:00",
      5: "2017-04-23 09:00:00",
      6: "2017-04-11 09:00:00",
      7: "2017-04-15 09:00:00"
  },
  {
    "name": "BETA",
    "user_id": 3,
    "escalas": {
      1: "2017-04-21 09:00:00",
      2: "2017-04-09 09:00:00",
      3: "2017-04-05 09:00:00",
      4: "2017-04-25 09:00:00",
      5: "2017-04-29 09:00:00",
      6: "2017-04-01 09:00:00",
      7: "2017-04-13 09:00:00",
      8: "2017-04-17 09:00:00"
    } 
  },
  {
    "name": "BETA",
    "user_id": 47,
    "escalas": {
      1: "2017-04-13 09:00:00",
      2: "2017-04-23 09:00:00",
      3: "2017-04-11 09:00:00",
      4: "2017-04-15 09:00:00",
      5: "2017-04-27 09:00:00",
      6: "2017-04-03 09:00:00",
      7: "2017-04-07 09:00:00",
      8: "2017-04-19 09:00:00"
    }
  }
]

How can I achieve this? I'm trying to do another query and trying to push it inside the collection made by the first query but is not working, so should be another way.

Thanks in advance.

Please sign in or create an account to participate in this conversation.