Ecto: Order by virtual / selected fields

Hey,

I am working on a library that manages existing resources. The user has to provide a schema and the library will display items based on certain options. This includes ordering, filtering and pagination. In an advanced setup the user is able to add a query which will then be executed before fetching items of the provided resource.

I built a generic ordering function with Ecto, but I still do not know how to handle scenarios where the user selects new fields in this query.

For example the user provides a user schema with username, first_name and last_name. In the query the user selects a field full_name with a fragment that combines first_name and last_name. Now, the user wants to order the items based on the full_name field.

Currently, I have something like:

def apply_criteria(query, criteria) do
    Enum.reduce(criteria, query, fn
      {:order, %{by: by, direction: direction}}, query ->
        query
            |> order_by({^direction, ^by}

       ...

      _, query ->
        query
    end)
  end

But this will not work with full_name, because the field is not part of the provided database schema.

How would I implement ordering by this field?

As far as I know the only way to order by such field is to copy the fragment into the order function, but I do not know which fragments or computations the user adds to the query and by which field the items should be ordered. I could use fragment("full_name") as the order field, but this doesn’t work, because fragment doesn’t allow strings to be interpolated. In addition not every computation is a fragment. It could also be something like a count the user maybe wants to add.

Best regards

1 Like

Not sure I completely understand, but there are a few ways you can make your order by statement dynamic:

  • a fragment. you don’t have to interpolate the first argument. you can do fragment("?", ^user_supplied_stuff) and the variable user_supplied_stuff will be interpolated where the question mark is
  • you can use selected_as to alias it and refer to that alias Ecto.Query.API — Ecto v3.9.1
  • you can use the dynamic macro Dynamic queries — Ecto v3.9.1
  • you can use the field macro to refer to fields in a schema dynamically Ecto.Schema — Ecto v3.9.1

Sorry not sure how to be more specific because your question is very broad. But hopefully some combination of these work for you.

1 Like

Thanks a lot!

a fragment. you don’t have to interpolate the first argument. you can do fragment("?", ^user_supplied_stuff) and the variable user_supplied_stuff will be interpolated where the question mark is

This works when I add a hardcoded string (e.g. fragment("full_name")), but as soon as I interpolate strings into the fragment, ordering is broken. This is because ecto adds parentheses. fragment("full_name") becomes full_name, but fragment("?", ^"full_name"), becomes 'full_name'.

In addition I do not want to rely on a fragment. With the fragment approach the user could not use a simple sum or count in his query to add computed fields.

you can use selected_as to alias it and refer to that alias

It is the same like the fragment approach.

Example

This works

from p in Post,
  select: %{
    posted: selected_as(p.posted, :date),
    sum_visits: p.visits |> coalesce(0) |> sum() |> selected_as(:sum_visits)
  },
  group_by: selected_as(:date),
  order_by: selected_as(:sum_visits)

but as soon as I interpolate values, the query is broken.
I tried

by = :sum_visits
order_by: selected_as(by)

and

by = :sum_visits
order_by: selected_as(^by)

but both of them will result into selected_as/1 expects name to be an atom

you can use the dynamic macro

I will look into the dynamic macro in more detail, but I think it will not solve my problem entirely.

you can use the field macro to refer to fields in a schema dynamically

I use the field macro to define a field on the schema, but how would I use the macro to refer to fields in a query?

1 Like

Sorry my bad I quoted the wrong field macro. This one lets you refer to a schema field dynamically: Ecto.Query.API — Ecto v3.9.1

Oh I see what you mean now. There is a literal macro that I think will let you interpolate without quotes. Does this one work for you: Ecto.Query.API — Ecto v3.9.1.

I tried this one already, but which schema I should refer to? The selected value (e.g. full_name) is not part of a schema directly, because the value is computed. It is part of the result set, but not part of any database table.

Yeah you can only use if when referring to schema fields. For non-schema fields it has to be fragment or selected_as. I see your point that the value used in selected_as has to be a compile time atom. Maybe the Ecto team can extend it to work with run-time atoms.

I get it running with the dynamic macro. The user has to provide the fragment and I will then use it for concatenation and ordering. Thank you!