Is using `count()` the right way for grouping and ordering?

SomeModule 
|> group_by([l], l.booking_id) 
|> select([l], %{ l.booking_id => fragment("array_agg(json_build_object('id', ? , 'status', ?, 'inserted_at', ?))", l.id, l.status, l.inserted_at)}) 
|> order_by([l], desc: count(l.inserted_at)) 
|> Repo.all

Is it right to use count(l.inserted_at) ???

This query gives me the data as i want

[
  %{
    "253cfc35-5547-4e8b-a9bb-9930c614002a" => [
      %{
        "id" => "b826f7a0-22a7-4901-a902-f6720b20509c",
        "inserted_at" => "2021-10-05T05:24:24.482395",
        "status" => "booked"
      },
      %{
        "id" => "107e38fd-cfc9-404d-97fc-98d91c566288",
        "inserted_at" => "2021-10-05T05:24:24.955932",
        "status" => "booked"
      },
      %{
        "id" => "81b50494-e364-4f7f-b0d7-9603bdfac13a",
        "inserted_at" => "2021-10-05T05:24:25.449173",
        "status" => "booked"
      }
    ]
  },
  %{
    "fda7fbe2-05fa-4b00-a3d7-1303cbb0030e" => [
      %{
        "id" => "bc743ea9-80ab-4763-8167-87e6a45bc5e8",
        "inserted_at" => "2021-10-05T05:21:53.780794",
        "status" => "booked"
      }
    ]
  }
]

Yes, this works.
From the top of my head, it is even cleaner is using a plain count(), which will sum up all entries (in the particular group).
Theoretically the count(l.inserted_at) might skip records where inserted_at is nil, for instance. (As inserted_at is automatically managed by most Ecto schema’s, it’s unlikely that this is ever the case, but it is possible.)

hi. I have a question in this case that if inserted_at can be nil sometimes. how can we handle both cases? Can we use something like a guard function when inserted_at is nil then count all the queries where it is nil and similarly if it is not nil then count all the records for that?

You can count something else than a field that is might be NULL. For example id. Or have a case-statement. But I would count id.

The order_by docs also suggest the possibility to include NULLs and put them at the front or at the end of the returned results, e.g.:

|> order_by([l], desc_nulls_first: count(l.inserted_at))

or

|> order_by([l], desc_nulls_last: count(l.inserted_at))

but what if I need to count them separately? There will be entries where one particular field can be nil.

Depends if you’re okay with 2 separate DB queries. If that’s fine then just make where clause’s for both cases. If you want only 1 query then use the above approach and count then programmatically (in the Elixir code).