Ordering Ecto query by fragment string in a variable

Questions / Help
1

Currently I have an Ecto query which is sorted by an order_by argument like so:

query =
  Ecto.Queryable.to_query(queryable_passed_in_as_an_argument)
  |> Ecto.Query.order_by(^order_by)

order_by is a keyword list of directions and field names, like [asc: :name, desc: :id]. This order_by arg is constructed by a different module from where the query is actually running.

However, in a more advanced case I would like to sort by an arbitrary fragment on the model, such as my_jsonb_column->>'Name'. This would be simple to do if I were running the entire query from the same place where the order_by was being constructed, like so:

from(i in Item, order_by: fragment("my_jsonb_column->>'Name' ASC"))

But that is not how this code is structured, and I can’t really restructure it like that. (If you want to know why, the real code where this is happening is in this GitHub comment. The order_by basically gets constructed when the arguments to the GraphQL API call are parsed, which is much earlier than when the eventual query is run.)

It appears that fragment/1 is not usable outside of a query function like from, and if I attempt to call fragment(^order_by_string) with order_by_string = "my_jsonb_column->>'Name' ASC", Ecto refuses as it thinks I’m opening myself to SQL injection:

     ** (ArgumentError) to prevent SQL injection attacks, fragment(...) does not allow strings to be interpolated as the first argument via the `^` operator, got: `["payload->>'Name' ASC"]`

Is there any way to order an Ecto query by an arbitrary string fragment when that string is in a variable and not available at compile time?

2

Try passing the column name as an atom? String.to_atom should help.

3

Unless I’m misunderstanding you, this is exactly what I’m already doing in the regular case:

And this works great in general, because afaik it is the only way to reuse order_by arguments in both a regular queryable and on a windowed query:

order_by = [asc: :id]

query = Ecto.Queryable.to_query(query)
  |> Ecto.Query.order_by(^order_by)

windowed_query =
  from(q in query,
    # TODO: dynamic primary key
    select: %{id: q.id, dense_rank: over(dense_rank(), :paginate)},
    windows: [paginate: [partition_by: ^partition_field, order_by: ^order_by]]
  )

My challenge isn’t to figure out how to sort by a regular column, as that’s already working. I want to be able to sort by an arbitrary fragment such as "my_jsonb_column->>'Name' ASC", and I want to be able to do so both on the main query and on windowed_query.

Is there any way to achieve this?

4

You have to introduce your own intermediate representation.

For example, you can implement all order by like this:

order_by = [
  {:asc, :field, :name},
  {:desc, :field, :id},
  {:asc, :json_access, :field, :key}
]

And then you traverse it changing the query:

Enum.reduce(order_by, query, fn
  {dir, :field, field}, query ->
    from q in query, order_by: [{^dir, field(q, ^field)}]

  {dir, :json_access, field, key}, query ->
    from q in query, order_by: [{^dir, fragment("?>>?", field(q, ^field), ^key)}]
end)

That’s basically how order_by: keyword works behind the scenes but since you want to pass custom commands, you have to build your own.

5 Likes
5

Thanks @josevalim, it does seem like that will work for the simple case of ordering the query itself. This works because I am able to take an existing query and continue appending new order_by clauses to it.

However, I’m not seeing how to apply that to the order_by inside my windows definition. It appears that I only get to define a window a single time, and there doesn’t seem to be a way to append additional order_by expressions to an existing window. Am I missing something or is that not possible?

To be clear: what I want to achieve is to be able to apply the same sort both to the base query, and also to the pagination window function.

6

I had one more idea for a workaround: trying to directly re-use the order_bys from one query in the window's order_by.

# Construct a regular query with multiple order_bys:
q1 = from(u in MyApp.User, order_by: [asc: :name, desc: :id]) |> order_by(desc: :another_field)
#Ecto.Query<from u0 in MyApp.User, order_by: [asc: u0.name, desc: u0.id], order_by: [desc: u0.another_field]>
iex(43)> q1.order_bys
[
  %Ecto.Query.QueryExpr{
    expr: [
      asc: {{:., [], [{:&, [], [0]}, :name]}, [], []},
      desc: {{:., [], [{:&, [], [0]}, :id]}, [], []}
    ],
    file: "iex",
    line: 0,
    params: []
  },
  %Ecto.Query.QueryExpr{
    expr: [desc: {{:., [], [{:&, [], [0]}, :another_field]}, [], []}],
    file: "iex",
    line: 42,
    params: []
  }
]

# Construct a query with order_bys in a window:
iex(45)> q2 = from(u in MyApp.User, windows: [ordered_names: [order_by: [asc: :name, desc: :id, desc: :another_field]]])
#Ecto.Query<from u0 in MyApp.User, windows: [ordered_names: [order_by: [asc: u0.name, desc: u0.id, desc: u0.another_field]]]>

iex(46)> q2.windows
[
  ordered_names: %Ecto.Query.QueryExpr{
    expr: [
      order_by: [
        asc: {{:., [], [{:&, [], [0]}, :name]}, [], []},
        desc: {{:., [], [{:&, [], [0]}, :id]}, [], []},
        desc: {{:., [], [{:&, [], [0]}, :another_field]}, [], []}
      ]
    ],
    file: "iex",
    line: 49,
    params: []
  }
]

# Attempt to turn q1.order_bys into the same format used by window order_by:
iex(47)> q1_order_bys = q1.order_bys |> Enum.reduce([], fn ob, acc -> acc ++ ob.expr end)
[
  asc: {{:., [], [{:&, [], [0]}, :name]}, [], []},
  desc: {{:., [], [{:&, [], [0]}, :id]}, [], []},
  desc: {{:., [], [{:&, [], [0]}, :another_field]}, [], []}
]


# However, Ecto.Query.Builder.OrderBy does not allow reuse like this:
iex(48)> q3 = from(u in MyApp.User, windows: [ordered_names: [order_by: ^q1_order_bys]])
** (ArgumentError) expected a field as an atom, a list or keyword list in `order_by`, got: `{{:., [], [{:&, [], [0]}, :name]}, [], []}`
    (ecto) lib/ecto/query/builder/order_by.ex:105: Ecto.Query.Builder.OrderBy.field!/2
    (ecto) lib/ecto/query/builder/order_by.ex:115: anonymous fn/2 in Ecto.Query.Builder.OrderBy.order_by!/2
    (elixir) lib/enum.ex:1327: Enum."-map/2-lists^map/1-0-"/2

As you can see, Ecto does not allow me to do this either. So AFAICT there is no way to apply arbitrary fragments from a variable to a window’s order_by.

7

I see.

I just pushed a commit to master that allows dynamic in order_by. So now you can do this:

      order_by = [
        asc: :some_field,
        desc: dynamic([p], fragment("?>>?", p.another_field, "json_key"))
      ]

      from query, order_by: ^order_by

You can also pass dynamic to the order_by inside a Window.

4 Likes
8

Can we rely on a forum announcement when this change makes it into the next minor version, please?

1 Like
9

To be honest, probably not. We do create a new release on GitHub, so you can consume that information. Anyone is also welcome to watch the issues tracker and do the announcements themselves. Maybe someone can write something that automates the process.

1 Like
10

Just making sure, you mean here, correct?

1 Like
11

Yes, exactly!

1 Like
12

@josevalim amazing, this is working perfectly! :exploding_head:

Now I can just pass around a keyword list like so and use it in both the main query’s order_by as well as the window order_by:

sort = [asc: dynamic([p], fragment("?->>?", p.payload, "Name")), asc: :id]

query = from(q in query, order_by: ^sort)

windowed_query =
  from(q in query,
    select: %{id: q.id, dense_rank: over(dense_rank(), :paginate)},
    windows: [
      paginate: [
        partition_by: ^partition_field,
        order_by: ^sort
      ]
    ]
  )

Thank you so much for adding this! :tada: :smile:

3 Likes