How to build a generic prepare statement for a read operation?

Hi everyone! I’m trying to build a generic prepare statement for a read operation. I’m working on a project with several “search” queries, and to include the search term on the query to postgress we use prepare. The goal here is to create a separate module with the prepare statement inside, so then we can call that module without having to duplicate code. The read operation would be something like this:

    read :list do
      argument :search_term, :string do
        allow_nil? true
        constraints min_length: 1, max_length: 50

      prepare {Preparations.SearchTerm, field: "title"}

and the module:

defmodule Preparations.SearchTerm do
  use Ash.Resource.Preparation
  require Ash.Query

  def init(opts) do
    if is_nil(opts[:field]) do
      {:error, "field is nil!"}
      {:ok, opts}

  def prepare(query, _, _) do
    with term <- Ash.Query.get_argument(query, :search_term) do
      search_term = "#{String.replace(term, ~r/\*%/, "\\\\1")}%"
      ## here's the error
      query |> Ash.Query.filter(ilike(opts[:field], ^search_term))
      _ ->

(“title” is an argument of the original module)
The thing is that when the query gets traslated to SQL, the field is translated as a literal string, which causes the query to don’t match with anything. I’ve tried by working with strings for the field and with atoms, with no success. The only way i could find to make it work was by referencing the actual title field on the ilike statement:

query |> Ash.Query.filter(ilike(title, ^search_term))

The problem with this is that it will only work for queries on tables with the “title” column on it. Not so generic.
Any clues on this? Any help is welcome!
Thanks! :smiley:

1 Like

Hi @Ulisesredi, and welcome to the forums. Your question relates to the Ash Framework, so probably best to post it there as that is monitored by the Ash Framework team. I would move it for you, but don’t have the forum privileges…

The Ash section is here: Ash Forum - Elixir Programming Language Forum

 query |> Ash.Query.filter(ilike(ref(^opts[:field]), ^search_term))

To dynamically refer to a field, you need to use ref/1 :slight_smile:

1 Like