Dynamic and ordered list of expressions in ecto query from user input, each input maps to a field or fragment

Questions / Help
,
1

I have a products api, database is postgresql, I use ecto.

curl "localhost:4000/products?brand=AZN&limit=1&fields=ean,date,brand"
{"data":[{"brand":"AZN","date":"2019-09-01T02:00:00Z","ean":"9783423214254"}]}

Accept can be text/csv, and then

  • format of date is milliseconds from epoch.
  • fields MUST be in order
curl "localhost:4000/products?brand=AZN&limit=1&fields=ean,date,brand" -H accept:text/csv
9783423214254,1567303200000,AZN

I do not know how to achieve that using select and select_merge.

When I add expression to select, one at a time using select_merge:

q = from p in Product, select: %{}
q = from p in q, select_merge: map(p, [:ean])
q = from p in q, select_merge: %{date: fragment("extract(epoch from ?)*1000", p.date)}
q = from p in q, select_merge: map(p, [:brand])
Repo.to_sql(:all, q)
{"SELECT p0.\"ean\", p0.\"brand\", extract(epoch from p0.\"date\")*1000 FROM \"products\" AS p0",
 []}

Then order is not preserved: ean, brand, date. I need ean, date, brand.
How would you do that?

Cheers.

2

You’re selecting fields as part of a map. Maps are unordered and I guess the order in the generated sql is an implementation detail. If you need order you probably need to use select and a list as wrapping type.

3

Thanks.

a) select only?
I’ll be glad to select a list.

list = ~w(ean date brand)a
q = from p in Product, select: ^list
Repo.to_sql(:all, q)
{"SELECT p0.\"ean\", p0.\"date\", p0.\"brand\" FROM \"products\" AS p0", []}

But I need to format the date field, not date but an expression like “extract(epoch from date)*1000”.

@LostKobrakai do you know how to do that?

??

q = from p in Product, select: Enum.map(^list, fn x -> x end)
** (Ecto.Query.CompileError) `Enum.map(^list, fn x -> x end)` is not a valid query expression. If you want to invoke Enum.map/2 in a query, make sure that the module Enum is required and that map/2 is a macro

??

list = [:ean, "extract(epoch from date)*1000", :brand]
from p in Product, select: ^list
** (ArgumentError) expected a list of fields in `select/2` inside `select`, got: `[:ean, "extract(epoch from date)*1000", :brand]`
    (ecto) lib/ecto/query/builder/select.ex:138: Ecto.Query.Builder.Select.fields!/2
    (ecto) lib/ecto/query/builder/select.ex:155: Ecto.Query.Builder.Select.select!/5

b) select + select_merge?

Maps are unordered and I guess the order in the generated sql is an implementation detail.

select_merge expects a map or struct in select, can not use a list.

Note that it does preserve ordering when there is no expression.

q = from p in Product, select: %{}
q = from p in q, select_merge: map(p, [:ean])
q = from p in q, select_merge: map(p, [:date])
q = from p in q, select_merge: map(p, [:brand])
Repo.to_sql(:all, q)
{"SELECT p0.\"ean\", p0.\"date\", p0.\"brand\" FROM \"products\" AS p0", []}

Go and see struct with the fragment?

%Ecto.Query.SelectExpr{
  expr: {:merge, [],
   [
     {:merge, [],
      [
        {:&, [], [0]},
        {:%{}, [],
         [
           date: {:fragment, [],
            [
              raw: "extract(epoch from ",
              expr: {{:., [], [{:&, [], [0]}, :date]}, [], []},
              raw: ")*1000"
            ]}
         ]}
      ]},
     {:&, [], [0]}
   ]},
  fields: nil,
  file: "iex",
  line: 6,
  params: [],
  take: %{0 => {:map, [:ean, :brand]}}
}

date fragment goes to .expr, ean and brand to .take, separate fields in struct, ordering can not be preserved.

Whatever detail, implementation does not preserve ordering in this use case.

4

Maps in elixir are not ordered, therefore you probably shouldn’t expect an ecto query producing a map to do so. Any ordering you might observe is an implementation detail, you shouldn’t rely on.

q = from p in Product, select: [
  p.ean,
  fragment("extract(epoch from ?)*1000", p.date),
  p.brand
]
2 Likes
5

therefore you probably shouldn’t expect an ecto query producing a map to do so.

For the csv api, I stream postgres copy csv.

    SQL.stream(Repo, copy(sql), [], max_rows: 1000)
    |> Stream.map(fn %{rows: rows} -> rows end)

  defp copy(sql) do
    "copy (#{sql}) to stdout with csv"
  end

There is no ecto processing, just stream the bytes to socket.
Performance is near raw psql copy.

input: fields=ean,date,brand
desired output: something similar to select ean, extract(epoch from date)*1000, brand.

I’m just failing to see how to use the query api to build that part of sql statement.

For the json api, I’m happy with Repo.all(q) and I do not care about ordering of fields in output (json, who cares?) nor sql.

6

Did you try the query I posted above? This one should give you the desired select statement.

7

Ideed, for user input fields=ean,date,brand the query from p in Product, select: [p.ean, fragment("extract(epoch from ?)*1000", p.date), p.brand] yields fields in desired order.

But I need to map user fields to selected expressions. When fields=ean,price,date then I should case to from p in Product, select: [p.ean, p.price, fragment("extract(epoch from ?)*1000", p.date)].

I would like to start with select: [] and reduce user inputs to expressions on query to build the equivalent queries above.