Ecto fragments - how to regex match dynamically?

Questions / Help
,
1

In order to apply some sort of pagination, I want to match the first letter of the department name column:

from(d in Department, where: fragment("? ~ '^[A-F].*'", d.name)) |> Repo.all()

This works fine.

I now want to change the matching list of characters dynamically:

from(d in Department, where: fragment("? ~ '^[?].*'", d.name, "A-F")) |> Repo.all()

Which errors with:

** (Ecto.Query.CompileError) `d.name(fragment("? ~ '^[?].*'", d.name(), "A-F"))` is not a valid query expression

Thanks for any suggestions :+1:

For context, here is the function I have written:

  def maybe_filter_dept_group(query, dept_group) when is_integer(dept_group) do
    query =
      from(engs in query,
        join: dept in Production.Department,
        on: engs.department_id == dept.id
      )

    case dept_group do
      1 -> from([e, ..., d] in query, where: fragment("? ~ '^[A-F].*'", d.name))
      2 -> from([e, ..., d] in query, where: fragment("? ~ '^[G-N].*'", d.name))
      3 -> from([e, ..., d] in query, where: fragment("? ~ '^[P-Z].*'", d.name))
    end
  end
2

The trick is to pass the whole regex as a param to the fragment

range = "A-F"
regex = "^[#{range}].*"
from([e, ..., d] in query, where: fragment("? ~ ?", d.name, ^regex))
7 Likes
3

That’s great, thanks!