Complex search query requires both `distinct` with `order_by`, best way to implement?

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.

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

  defp search(query, opts) do
        |> 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],
        |> search_function(val)

  defp search_function(query, value) do
    str = "%#{value}%"

    |> where(
      [order: o, user: user, prod: prod],
      ilike(, ^str) or
        ilike(, ^str) or
        ilike(user.firstname, ^str) or
        ilike(user.lastname, ^str) or
        ilike(, ^str) or
        ilike(, ^str)

  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,}])

      [direction, col] ->
       order_by(query, [order: o], {^direction, ^col})

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.

Do a group_by([order: o], instead of distinct and you should be fine.

(sorry if this syntax isn’t quite right, I’m not very familiar with the macro syntax)

PS: If you’re using Postgres or sqlite you should look into using fulltext search. It’s much more performant and results in simpler queries.