I’m implementing a customer search function; a customer can search through their previous orders. The search is fairly broad and includes things like the name of the user who placed the order, the order number, but you can also search for a product in the order an Order line item.
Order has_many OrderLineItems so to search for an order by an order line item you have to join OrderLineItems. However adding distinct breaks order_by/3, sorting no longer works when there is a query string.
Order
|> from(as: :order)
|> where([order: o], o.customer_id == ^id)
|> search(opts)
|> preload(
user: [:user_credentials],
address: [],
order_line_items: [],
)
|> order(opts)
|> Repo.paginate(opts)
end
defp search(query, opts) do
query
|> join(:left, [order: o], u in assoc(o, :user), as: :user)
|> join(:left, [order: o], oli in assoc(o, :order_line_items), as: :oli)
|> join(:left, [obi: obi], prod in assoc(obi, :product, as: :prod)
# I feel like I have to include `distinct/3` otherwise we'll get as many `Orders` as there `OrderLineItems`
|> distinct([order: o], o.id)
|> search_function(val)
end
end
defp search_function(query, value) do
str = "%#{value}%"
query
|> where(
[order: o, user: user, prod: prod],
ilike(o.id, ^str) or
ilike(user.email, ^str) or
ilike(user.firstname, ^str) or
ilike(user.lastname, ^str) or
ilike(prod.name, ^str) or
ilike(prod.id, ^str)
)
end
defp order(query, opts) do
case Keyword.get(opts, :sort, nil) do
nil ->
order_by(query, [order: o], [{:desc, coalesce(o.placed_on, o.inserted_at)}, {:desc, o.id}])
[direction, col] ->
order_by(query, [order: o], {^direction, ^col})
end
end
If I apply distinct more broadly like distinct(true) then I get an error: (invalid_column_reference) for SELECT DISTINCT, ORDER BY expressions must appear in select list.
I understand that distinct adds an order clause but I’m not sure of the best way around this. The snippet I shared is only a part of the query, in reality there are about five other ilike matchers and we join a few other tables to the order (although order_line_items is the only one with a has_manyassociation. Based on that I feel like explicitly adding each column that could be searched to theorder_by` clauses would be unmanageable.
