Ecto where like query acts like where ==

Question

I'm trying to get an ecto like query working like this:

def find(searchterm) do
  query = from c in Contact, 
  #where: fragment("? % ?", c.company_name, ^searchterm),
  where: like(c.company_name, ^searchterm),
  contacts = Repo.all(query)
  {:ok, contacts}
end

In my table, I have a company_name "Asymptote". Using where: like/2 my query looks like this:

SELECT c0."id", c0."company_id", c0."company_name" FROM "contacts" AS c0 WHERE (c0."company_name" LIKE $1) ["Asym"] (1.0ms)

when the pg_trm search uncommented, it looks like this:

SELECT c0."id", c0."company_id", c0."company_name" FROM "contacts" AS c0 WHERE (c0."company_name" % $1) ["Asym"] (1.0ms)

As far as I can see, the queries look good, but there are no results. Since I added the index after adding "Asymptote" to the database, I expect that is why it isn't found in the pg_trm index, but why won't like/2 or ilike/2 work? When entering in the full name "Asymptote", I am able to find the record.

Answer 1

I faced some similar problem. Unfortunately I had no pg_trgm available. I used the LIKE as in:

  from candidate in query,
  where: like(candidate.first_name, ^("%#{text}%"))

This matched the text in any place of the candidate.first_name.