Htykuut

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

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).