Building an Ecto macro to generate a row value

Questions / Help
,
1

So I’m writing a query (with Postgres) that uses a row value/composite value. For a little background see https://use-the-index-luke.com/blog/2013-07/pagination-done-the-postgresql-way slide 22.

My query looks roughly like this:

SELECT first_name, role, inserted_at
FROM users
WHERE (ARRAY_POSITION(array['admin', 'member', 'previous_member'], role::text), inserted_at) >= (1, '2018-07-04 09:01:25.320614')
ORDER BY ARRAY_POSITION(array['admin', 'member', 'previous_member'], role::text) DESC, inserted_at ASC;

The array_position call isn’t too important here but it allows me to define a custom order for a postgres enum: https://stackoverflow.com/questions/4088532/custom-order-by-explanation

The important part of the query above would look like this with basic sorting, the where clause: WHERE (role, inserted_at) >= (1, '2018-07-04 09:01:25.320614'), and the order by: ORDER BY role DESC, inserted_at ASC

Since as far as I can tell Ecto doesn’t have a built-in macro to generate these row values (basically just comma-separated expressions) I want to create a macro to generate my own. However this is complicated by the fact that I want to create a macro that would work with a row value of any size, so rather than just 2 parts as above, it should support 3 as well.

A basic fragment for a 2-part row value looks like:
fragment("(?, ?) >= (?, ?), arg1, arg2, arg3, arg4)
and for a 3-part row value it would look like:
fragment("(?, ?, ?) >= (?, ?, ?)", arg1, arg2, arg3, arg4, arg5, arg6)

But also you’d want to pass in the operator instead of hard-coding it to ">=" so you need one more argument. And of course Elixir doesn’t have variadic functions so you have to pass in a list. And rather than pass in all the arguments in the same order that they appear in the query I think it would make sense to pass them in with their associated counterpart, so a list of tuples:

If the SQL looked like (colA, colB) >= (valA, valB) then the list would look like [{colA, valA}, {colB, valB}]

So with that out of the way this is how I would like to call the macro in a where query:

where(query, [user], row_value_comparison(">=", [{user.role, ^role}, {user.inserted_at, ^inserted_at}]))

But I’m having trouble defining the row_value_comparison macro, specifically I’m running into trouble trying to call fragment with a variable amount of arguments, would Kernel.apply/3 work here?

2 Likes
2

No it will not, as fragment isn’t function, it isn’t macro either. It is weird construct that is available only within from macro expansion. Here you can see how I achieved something similar for my defqueryfunc macro. It generates expected fragment in runtime. However in your example I believe you could use “more natural” syntax by defining only to_row/1 macro that would take tuple/list and would return proper fragment as Ecto should take care of comparing values for you.

2 Likes
3

What about something like this (typed entirely in-post so untested, not even compiled, so some light massaging may be needed):

defmacro row_value_comparison(op, args) when is_binary(op) and is_list(args) do
  keys = Keyword.keys(args)
  values = Keyword.values(args)
  markers = "(" <> Enum.map_join(keys, ",", fn _ -> "?" end) <> ")"
  frag_text = markers <> op <> markers
  quote(do: fragment(unquote(frag_text), unquote_splicing(keys ++ values)))
end

Then you ‘should’ be able to use it like:

where(query, [user], row_value_comparison(">=", [{user.role, ^role}, {user.inserted_at, ^inserted_at}]))
3 Likes
4

Thanks for this sample code. unquote_splicing looks like the missing piece. Here’s my final implementation that is fairly similar but I didn’t feel comfortable using Keyword functions on something that isn’t actually a keyword list:

defmodule Utils.QueryFunctions do
  @doc """
  This function allows you to efficiently cursor paginate through a table
  based on two columns/expressions at once

  Arguments:
  * `opertation` is a string that represents a postgres comparison function such
    as ">=" or "<"
  * `comparisons` is a list of tuples where the first element in the tuple is the
    expression that you're testing, and the second element is what you are
    testing against. Similar to a keyword list, but without atoms.

  This query uses row values, a good example is found on slide 22 of:
  https://use-the-index-luke.com/blog/2013-07/pagination-done-the-postgresql-way

  More background info: https://elixirforum.com/t/building-an-ecto-macro-to-generate-a-row-value/19680/2
  """
  defmacro row_value_comparison(operation, comparisons)
           when is_binary(operation) and is_list(comparisons) do
    question_marks = Enum.map(comparisons, fn _ -> "?" end) |> Enum.join(", ")
    frag_text = "(#{question_marks}) #{operation} (#{question_marks})"

    frag_arguments =
      comparisons
      |> Enum.unzip()
      |> Tuple.to_list()
      |> List.flatten()

    quote do
      fragment(unquote(frag_text), unquote_splicing(frag_arguments))
    end
  end
end

I could probably name the question_marks variable better :sweat_smile:

2 Likes
5

Mine wasn’t any better, markers. ^.^;

I’m not sure what a good name for that would be… ^.^;

But yep, unquote_splicing is super useful, otherwise you have to build the entire argument list in a single quote, which though doable, is not as clear. :slight_smile:

2 Likes
6

Ooh, this is cool — I’m trying to do the same thing (implementing row-value–based pagination). However, I’m trying to do this dynamically, such that the operation and comparisons are provided as runtime parameters (use case: allowing the end user of the API to specify what sort they want). Ideally, I’d like to be able to do this, but this doesn’t seem to work with macro interpolation:

def slice_query(existing_query, cursor_params)
  operator, comparisons = parse_params(cursor_params) # Hand-wavy bit
  existing_query |> where(row_value_comparison(operator, comparisons))
end

Any thoughts on how this might be made to work?

1 Like
7

Did you ever figure this out?

8

I wasn’t able to figure it out using Ecto’s DSLs for queries (I still find them syntactically and conceptually confusing). Instead, I realized that Ecto.Adapters.SQL.query/4 is a thing, so I ended up with an implementation that worked with SQL directly — I built a struct representing SQL queries (but as a struct so I could manipulate clauses, e.g. adding a SELECT field or WHERE condition), a function to turn a struct into SQL string, and then I call that query function to get the results and map them back into my Ecto models based on the SELECT/FROM of the SQL struct.

9

But Ecto queries allow comparing tuples, so if you want do do:

SELECT
  -- …
FROM table t
WHERE (t.a, t.b) >= (1, 2)

You can do

from t in "table",
  where: {t.a, t.b} >= {1, 2},
  select: # …
10

IIRC, the problem I ran into is that I want the field names to be dynamic (i.e. at runtime, the user can indicate which fields they want to sort by (including more than one), and at runtime I wanted to build up the left and right side of that where). I couldn’t get that to work with Ecto’s macro syntax (i.e. something like where: ^sort_fields >= ^sort_order resulted in a compile-time error).

11

Afaik this is not supported right now, unless you build a macro for each length of tuple you might get. There’s just not a good way to dynamically pass a tuple of flexible size with column aliases to an ecto query without interpolating into the query, which is prohibited.

1 Like