Is it possible to generate a query clause that combines data from multiple db columns using Ecto.Query?

Question

I have a simple Postgres table that stores first_name and last_name for Person records. Let's say it contains the following example data:

first_name	last_name
John	Smith
Jane	Doe
Bob	Smith
Mark	Johnson
John	Wayne

I also have the following function for searching up the records given a search string name:

  def search_persons_by_name(name) do
    query = "%#{name}%"

    Person
    |> where([p], ilike(p.first_name, ^query))
    |> or_where([p], ilike(p.last_name, ^query))
    |> Repo.all
  end

The function works as expected, in that if you search up a name, it'll check to see if any record's first_name or last_name matches, and return the record(s) accordingly. For example, with the search string "john", the function will return the record objects for "John Smith" and "John Wayne".

The limitation I want to address is that if name is given as "john smith", instead of returning the record for "John Smith", it produces an empty list instead. I attempted to modify the query like this:

  def search_persons_by_name(name) do
    query = "%#{name}%"

    Person
    |> where([p], ilike(p.first_name, ^query))
    |> or_where([p], ilike(p.last_name, ^query))
    |> or_where([p], ilike("#{p.first_name} #{p.last_name}", ^query))
    |> Repo.all
  end

But this would not even compile, and gives me the error: ** (Ecto.Query.CompileError) \`"#{wp.first_name} #{p.last_name}"` is not a valid query expression. Only literal binaries and strings are allowed, dynamic values need to be explicitly interpolated in queries with ^

I had partial success by doing the following:

  def search_persons_by_name(name) do
    all_names = String.split(name, " ", trim: true)

    Person
    |> where([p], p.first_name in ^all_names or p.last_name in ^all_names)
    |> Repo.all
  end

But the problem here is that, without utilizing ilike, the search string name must now be case-aware, which also isn't ideal.

I then tried to add a select clause into my query, like:

  def search_persons_by_name(name) do
    all_names = String.split(name, " ", trim: true) |> Enum.each(fn s -> String.downcase(s) end)

    Person
    |> select([p.first_name |> String.downcase |> selected_as(:first_name_lower)])   # only first_name just to try
    |> where([p], selected_as(:first_name_lower) in ^all_names)
    |> Repo.all
  end

But this also fails to compile with the error ** (Ecto.Query.CompileError) `String.downcase(p.first_name)` is not a valid query expression..

What I've been able to gather is that the string being searched in ilike and select cannot be manipulated by elixir functions, since doing so causes a compilation error.

So I'd like some help in achieving this.

Answer 1

I found an adequate workaround:

  def search_persons_by_name(name) do
    query = "%#{name}%"
    all_names = String.split(name, " ", trim: true) |> Enum.map(fn s -> String.downcase(s) end)

    Person
    |> where([p], ilike(p.first_name, ^query) or ilike(p.last_name, ^query))
    |> or_where([p], fragment("lower(?)", p.first_name) in ^all_names and fragment("lower(?)", p.last_name) in ^all_names)
    |> Repo.all
  end

Specifically by sending a SQL "fragment" and bypassing Ecto. This allows transformation of the column value before doing any comparisons against some other value.