GroupBy DB:raw not returning correct results

my query

 $schedules = DB::table('schedules')
                		->where('schedules.user_id', '=', Auth::id())
          				->get();

returns 8 results which are correct

[{"id": 4, "user_id": 3, "event_id": 5, "uuid": "f45cd59b-d824-4974-9252-6123a8fecc3f", "link": "https:\/\/meet.google.com\/qzg-fhrf-ccb", "created_at": "2023-06-10 15:53:17", "updated_at": "2023-06-10 15:53:17", "name": null }, {"id": 5, "user_id": 3, "event_id": 5, "uuid": "f4cf12bc-8348-40eb-ade8-4aa1bfbb0f99", "link": "https:\/\/meet.google.com\/yza-nzva-qnk", "created_at": "2023-06-11 13:27:43", "updated_at": "2023-06-11 13:27:43", "name": null }, {"id": 6, "user_id": 3, "event_id": 5, "uuid": "80af1b0b-3010-479c-aed0-c5739baa6beb", "link": "https:\/\/meet.google.com\/msr-wzvu-kgt", "created_at": "2023-06-11 13:30:37", "updated_at": "2023-06-11 13:30:37", "name": null }, {"id": 7, "user_id": 3, "event_id": 5, "uuid": "38e0f6a4-6962-44d2-876b-ff3a245e6d1f", "link": "https:\/\/meet.google.com\/edg-bhjq-mbu", "created_at": "2023-06-11 13:41:39", "updated_at": "2023-06-11 13:41:39", "name": null }, {"id": 8, "user_id": 3, "event_id": 5, "uuid": "f8d34dc5-3da3-4d6b-ac80-05a1bee47759", "link": "https:\/\/meet.google.com\/hyr-fgqh-jtt", "created_at": "2023-06-11 13:45:54", "updated_at": "2023-06-11 13:45:54", "name": null }, {"id": 9, "user_id": 3, "event_id": 5, "uuid": "800de84a-cd8b-4fab-b258-f35139c1b204", "link": "https:\/\/meet.google.com\/pfx-vgfj-fou", "created_at": "2023-06-11 14:00:59", "updated_at": "2023-06-11 14:00:59", "name": null }, {"id": 11, "user_id": 3, "event_id": 5, "uuid": "8e94badb-1ba1-484f-9c8d-1f324d899a0a", "link": "https:\/\/meet.google.com\/dwj-fdqi-jvp", "created_at": "2023-06-12 12:08:31", "updated_at": "2023-06-12 12:08:31", "name": "Google Meet" }, {"id": 12, "user_id": 3, "event_id": 5, "uuid": "92cac37d-9f54-494e-857f-c6b605091b2b", "link": "https:\/\/meet.google.com\/ark-gtyo-kev", "created_at": "2023-06-12 12:15:45", "updated_at": "2023-06-12 12:15:45", "name": "Google Meet" }]

but this query returns 3

 $schedules = DB::table('schedules')
                    ->where('schedules.user_id', '=', Auth::id())
                    ->select(DB::raw('DATE(schedules.created_at) as date'), DB::raw('MAX(schedules.link) as link'))
                    ->groupBy('date')
                    ->get();
[
  {
    "date": "2023-06-10",
    "link": "https:\/\/meet.google.com\/qzg-fhrf-ccb"
  },
  {
    "date": "2023-06-11",
    "link": "https:\/\/meet.google.com\/yza-nzva-qnk"
  },
  {
    "date": "2023-06-12",
    "link": "https:\/\/meet.google.com\/dwj-fdqi-jvp"
  }
]

what i am doing that is wrong

LaryAI

2 years ago

Level 58

The query is grouping the results by the date and selecting the maximum link for each date. This means that if there are multiple links for a single date, only the maximum link will be returned. If you want to return all links for each date, you can use the GROUP_CONCAT function instead of MAX. Here's an example:

$schedules = DB::table('schedules')
    ->where('schedules.user_id', '=', Auth::id())
    ->select(DB::raw('DATE(schedules.created_at) as date'), DB::raw('GROUP_CONCAT(schedules.link) as links'))
    ->groupBy('date')
    ->get();

This will return an array of objects, where each object has a "date" property and a "links" property. The "links" property will be a comma-separated string of all links for that date.

tykus

2 years ago

Level 104

What result are you expecting; why are you grouping?

TimiAde

2 years ago

Level 7

@tykus i am expecting

"2023-06-10":[
	{
	    "link": "https:\/\/meet.google.com\/qzg-fhrf-ccb"
	},
	{
	    "link": "https:\/\/meet.google.com\/qzg-fhrf-ccb"
	},
],
"2023-06-11":[
	{
	    "link": "https:\/\/meet.google.com\/qzg-fhrf-ccb"
	},
	{
	    "link": "https:\/\/meet.google.com\/qzg-fhrf-ccb"
	},
	....
],

and so on. What Lary AI gave me is

[
  {
    "date": "2023-06-10",
    "links": "https:\/\/meet.google.com\/qzg-fhrf-ccb"
  },
  {
    "date": "2023-06-11",
    "links": "https:\/\/meet.google.com\/yza-nzva-qnk,https:\/\/meet.google.com\/msr-wzvu-kgt,https:\/\/meet.google.com\/edg-bhjq-mbu,https:\/\/meet.google.com\/hyr-fgqh-jtt,https:\/\/meet.google.com\/pfx-vgfj-fou"
  },
  {
    "date": "2023-06-12",
    "links": "https:\/\/meet.google.com\/dwj-fdqi-jvp,https:\/\/meet.google.com\/ark-gtyo-kev"
  }
]

tykus

2 years ago

Level 104

@TimiAde that would require grouping the Collextion, not grouping in the query

 $schedules = DB::table('schedules')
   ->where('schedules.user_id', '=', Auth::id())
   ->selectRaw('DATE(created_at’,) as date, link')
   ->get()
   ->groupBy(fn($schedule) => $schedule->date);

Please or to participate in this conversation.