Hello, first post here. Sorry I’m not good at explaining things.
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.
Here is relevant code in sort_bus_list in buses.ex
#V1 - 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)
#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])
Here’s sudo 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_bus_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 duplicate
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