Cannot read params during complie time

Questions / Help
,
1

I am trying to sort the query result according to most similar to the field specify.
Example 1:

terms = "john phoenix"
Repo.all(c in Contact, order_by: similarity_order([c.name, c.city], terms)

Will generate SQL:

select * 
  from Contact c 
 order by COALESCE(SIMILARITY(c.name,'john'),0) + COALESCE(SIMILARITY(c.name,'phoenix'),0) + 
          COALESCE(SIMILARITY(c.city,'john'),0) + COALESCE(SIMILARITY(c.city,'phoenix'),0)

Example 2:

terms = "john phoenix denver"
Repo.all(c in Contact, order_by: similarity_order([c.name, c.city, c.state], terms)

Will generate SQL:

select * 
  from Contact c 
 order by COALESCE(SIMILARITY(c.name,'john'),0) + 
          COALESCE(SIMILARITY(c.name,'phoenix'),0) + 
          COALESCE(SIMILARITY(c.name,'denver'),0)+
          COALESCE(SIMILARITY(c.city,'john'),0) + 
          COALESCE(SIMILARITY(c.city,'phoenix'),0)+
          COALESCE(SIMILARITY(c.city,'denver'),0) + 
          COALESCE(SIMILARITY(c.state,'john'),0) + 
          COALESCE(SIMILARITY(c.state,'phoenix'),0)+
          COALESCE(SIMILARITY(c.state,'denver'),0)

I don’t seem to be able to compile.

I understand that during compile time param ‘terms’ is nil.

I don’t seem to be able to get ‘terms’ during runtime.

def search_contacts(terms) do
    Repo.all(
        from cont in Contact,
        order_by: similarity_order([cont.name, cont.city], ^terms)
end

defmacro similarity_order(fields, terms) do
  texts = String.split(terms, " ")

  frag_text =
    Enum.map(
      1..(Enum.count(fields) * Enum.count(texts)),
      fn _ -> "COALESCE(SIMILARITY(?,?),0)" end)
    |> Enum.join("+")

  fld_values = Enum.flat_map(fields, fn f -> Enum.flat_map(texts, fn t -> [f, t] end) end)

  quote do
    fragment(unquote(frag_text), unquote_splicing(fld_values))
  end
end
2

What you’re trying to do is not possible using a fragment. You cannot build fragments based on an unknown number of elements (your split terms). The string part (first parameter) of the fragment is required to be compile time known (as in a static not runtime changing string) and therefore the number of parameters also cannot change at runtime, if the string of SQL can’t change.

The defmacro code is only executed once at compile time. This code is no longer available at runtime.

You might be able to make it work using the dynamic macro though.

3 
import Ecto.Query

build_dynamic = fn columns, terms -> 
  for col <- columns, term <- String.split(terms, " ") do
    dynamic([c], fragment("COALESCE(SIMILARITY(?,?),0)", field(c, ^col), ^term))
  end
  |> Enum.reduce(fn a, b -> dynamic(^a + ^b) end)
end

order_by = [asc: build_dynamic.([:name, :city], "john phoenix")]
from c in "contacts", order_by: ^order_by, select: map(c, [:id])
# #Ecto.Query<from c0 in "contacts",
#  order_by: [
#   asc:
#     fragment("COALESCE(SIMILARITY(?,?),0)", c0.city, ^"phoenix") +
#       (fragment("COALESCE(SIMILARITY(?,?),0)", c0.city, ^"john") +
#          (fragment("COALESCE(SIMILARITY(?,?),0)", c0.name, ^"phoenix") +
#             fragment("COALESCE(SIMILARITY(?,?),0)", c0.name, ^"john")))
# ],
#  select: map(c0, [:id])>

order_by = [asc: build_dynamic.([:name, :city, :state], "john phoenix denver")]
from c in "contacts", order_by: ^order_by, select: map(c, [:id])
# #Ecto.Query<from c0 in "contacts",
#  order_by: [
#   asc:
#     fragment("COALESCE(SIMILARITY(?,?),0)", c0.state, ^"denver") +
#       (fragment("COALESCE(SIMILARITY(?,?),0)", c0.state, ^"phoenix") +
#          (fragment("COALESCE(SIMILARITY(?,?),0)", c0.state, ^"john") +
#             (fragment("COALESCE(SIMILARITY(?,?),0)", c0.city, ^"denver") +
#                (fragment("COALESCE(SIMILARITY(?,?),0)", c0.city, ^"phoenix") +
#                   (fragment("COALESCE(SIMILARITY(?,?),0)", c0.city, ^"john") +
#                      (fragment("COALESCE(SIMILARITY(?,?),0)", c0.name, ^"denver") +
#                         (fragment("COALESCE(SIMILARITY(?,?),0)", c0.name, ^"phoenix") +
#                            fragment("COALESCE(SIMILARITY(?,?),0)", c0.name, ^"john"))))))))
# ],
#  select: map(c0, [:id])>

dynamic = "a b c"
order_by = [asc: build_dynamic.([:name, :city, :state], dynamic)]
from c in "contacts", order_by: ^order_by, select: map(c, [:id])
# #Ecto.Query<from c0 in "contacts",
#  order_by: [
#   asc:
#     fragment("COALESCE(SIMILARITY(?,?),0)", c0.state, ^"c") +
#       (fragment("COALESCE(SIMILARITY(?,?),0)", c0.state, ^"b") +
#          (fragment("COALESCE(SIMILARITY(?,?),0)", c0.state, ^"a") +
#             (fragment("COALESCE(SIMILARITY(?,?),0)", c0.city, ^"c") +
#                (fragment("COALESCE(SIMILARITY(?,?),0)", c0.city, ^"b") +
#                   (fragment("COALESCE(SIMILARITY(?,?),0)", c0.city, ^"a") +
#                      (fragment("COALESCE(SIMILARITY(?,?),0)", c0.name, ^"c") +
#                         (fragment("COALESCE(SIMILARITY(?,?),0)", c0.name, ^"b") +
#                            fragment("COALESCE(SIMILARITY(?,?),0)", c0.name, ^"a"))))))))
# ],
#  select: map(c0, [:id])>
1 Like
4

Wow… Your solution works well. Thank you.

One more question

is this possible, order field from 2 two tables?

Example: (I know this example won’t work)

from c in "contacts", join: dtl in "details", on: c.id == dtl.contact_id,
order_by: ^build_dynamic.(["c.name", "dtl.city"], "john arkansas"), select: map(c, [:id])
5

dynamic([a, b], …) similar to how you’d do it when composing queries.