Ecto where a similar request acts like where ==

Question

I am trying to get an ecto-like request 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 the Asymptot company name. Using where: for example / 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 pg_trm search is 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 Asymptot to the database, I expect that is why it is not found in the pg_trm index, but why not like / 2 or ilike / 2? When I enter the full name of Asymptot, I can find the entry.

Answer 1

I had a similar problem. Unfortunately, I did not have pg_trgm. I used LIKE as in:

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

This matches the text anywhere in the candidate .first_name.

Answer 2

With the help of Mitchell Henke of Rokkincat, here is a way to get pg_trgm to work with custom match percentages:

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

But I still can't work like / 2. Also, I don’t see where in the Ecto source this function exists.