Ecto: Order by virtual / selected fields

Questions / Help
1

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
2

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

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

3 Likes
3

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
4

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

5

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

6

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.

7

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.

8

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!

9

just for the hell of it, here’s my take on this.

  1. fullname does not exist in the table in question, but
  2. first_name & last name do exist in the table, therefore
  3. create a functions where these two fields are part of the sort as concatenated fields and then
  4. return to the user the output of the sorted fields a s the full_name field…

my thoughts…