Sorting column must appear in the GROUP BY clause

Question

I'm using a postgresql database in my phoenix application. I have a table of buses, each of which is associated with one or more stops and routes. I'm trying to make a searchable list of all stops, but I don't want to re-write my searching and filtering functions (see list_filtered_buses).

So, in sort_bus_list, I'm trying to return a sorted list of all stops. My issue with the current implementation is that I get back duplicates when I render the list in my template since each bus is being returned with all its stops. I've tried two different approaches, one returning duplicates, and one preventing sorting due to an error.

Here is relevant code in sort_bus_list in buses.ex:

  # V1 - sortable, but returns duplicates
  list
    |> order_by([b, s, r], {^attrs.sort_dir, field(s, ^attrs.sort_attr)})
    |> group_by([b, s, r], [s.id, b.id])

  # V2 - doesn't return duplicates, but isn't returns error:
  # ERROR 42803 (grouping_error): column "b1.stop_id" must appear in the GROUP BY clause or be used in an aggregate function
    list
    |> order_by([b, s, r], {^attrs.sort_dir, field(s, ^attrs.sort_attr)})
    |> group_by([b, s, r], b.id)

Here's pseudo-code of my template:

for bus in Buses
    for stop in bus
        display stop
    end
end 

More buses.ex code for context:

 def list_filtered_buses(params) do
  search_term = params["filter"]["query"]

  from(t in Bus, join: a in assoc(t, :stops), join: c in assoc(t, :routes))
  |> search(search_term)
  |> list_buses_by_active(params)
  |> list_buses_by_type(params)
  |> sort_buses_list(params)
  |> preload([:stops, :routes])
 end


def sort_bus_list(list, params) do
  attrs = process_sorting_metrics(params)

  case params["filter"]["sort_attr"] do
    a when a == "stop_name" or a == "stop_id" ->

  #V2 - sortable, but returns duplcate
  list
    |> order_by([b, s, r], {^attrs.sort_dir, field(s, ^attrs.sort_attr)})
    |> group_by([b, s, r], [s.id, b.id])

  "route_name" ->
    list
    |> order_by([b, s, r], {^attrs.sort_dir, field(r, ^attrs.sort_attr)})
    |> group_by([b, s, r], [b.id, r.id])

  _ ->
    list
    |> order_by([b, s, r], {^attrs.sort_dir, field(b, ^attrs.sort_attr)})
    |> group_by([b, s, r], b.id)
  end
end

Answer 1

As it seems, you're trying to render only Stop. In the list_filtered_buses function, you're running a query on Bus table. Then, further you're trying to sort and group the data. The #V1 function is doing a group on Bus.id and Stop.id so you get duplicate Stops in case different Buses have same Stops. The #V2 function however returns error because you're doing a select query on Bus and you're doing group only on Bus.id and trying to render attributes from Stop.

As @AlekseiMatiushkin suggested, in order to render only distinct Stops, you need to do a from(s in Stop).