CODEWITHSUNDEEP
elixirecto
Jarmo Pertman
Jarmo Pertman

Reputation: 1905

Extracting Ecto fragment into variable

I have a fragment, which is repetitive and quite complex. I would like to extract it into variable/function and reuse it multiple times instead of writing it more than once. Here's one simplified example what I tried so far among with many different approaches:

f = dynamic([u], fragment("order by name desc"))
query = from(u in User)
        |> order_by([u], ^f)
Repo.all(query)

According to documentation it should be possible by using dynamic, but however I try within order_by, select, having or whatsoever I get an error message similar to:

(ArgumentError) expected a field as an atom, a list or keyword list in `order_by`, got: `dynamic([u], fragment("order by name desc"))`

How would I achieve it?

Update 1

Here's more real-life example. The following code works:

    from(p in Photo,
      join: pv in assoc(p, :v),
      left_join: pl in assoc(p, :l),
      left_join: pc in assoc(p, :c),
      select: %{p | last_activity_at: fragment("greatest(?, ?, ?)", max(p.inserted_at), max(pl.inserted_at), max(pc.inserted_at))},
      group_by: p.id,
      order_by: [desc: fragment("greatest(?, ?, ?)", max(p.inserted_at), max(pl.inserted_at), max(pc.inserted_at))]
    )

However, I cannot get it to work by extracting fragment out or using it with dynamic or whatever.

Upvotes: 1

Views: 1043

Answers (1)

Aleksei Matiushkin
Aleksei Matiushkin

Reputation: 121000

There is an example for order_by in the very same documentation for Ecto.Query.dynamic/2

f = dynamic([u], fragment("order by name desc"))
# order_by = [desc: :name] # why not just this?
order_by = [desc: ^f]
query = from(u in User), order_by: ^order_by
Repo.all(query)

For the updated example:

order = dynamic(
  [p, pl, pc], 
  fragment(
    "greatest(?, ?, ?)",
    max(p.inserted_at),
    max(pl.inserted_at),
    max(pc.inserted_at)
  )
)

from(
  p in Photo,
  join: pv in assoc(p, :v),
  left_join: pl in assoc(p, :l),
  left_join: pc in assoc(p, :c),
  select: ...,
  group_by: p.id,
  order_by: ^[desc: order]
)

As it is stated in the documentation,

it is not possible to pass dynamics outside of a root. For example, this won't work:

 from query, order_by: [asc: ^dynamic(...)]

But this will:

from query, order_by: ^[asc: dynamic(...)]

Upvotes: 1

Related Questions