Ecto query using like/ilike in query

Have you taken a look at the documentation? Ecto.Query.API — Ecto v3.14.0

From how I read it, you need to pass in a string as second argument, eg. like(u.product_name, "%SurfacePro%"), but you wan’t this probably set dynamically at runtime from user input.

Since I’m not sure if you are allowed to interpolate a string in the query the way you would do it outside, I’ll prepare it and then pin:

product = "SurfacePro" # or from a function argument?
like = "%#{product}%"

from(u in Product,
  where: like(u.product_name, ^like),
  select: %{product_id: u.id, description: u.product_desc}
) |> Repo.all

Hello @NobbZ,

Many thanks.
Your suggestion worked.

I had tried several different options without success.

Regards,

Jerry

Can someone please guide me here, below is my requirement:-

iex:8> Repo.all(from u in AdminUser, select: u.roles)

This query gives the below result:-
[
[“root”],
[“super”, “transfer”, “operator”],
[“super”, “transfer”, “operator”],
[“super”, “operator”],
[“root”]
]

This is working to get the count for “root” as 2.
Repo.one(from u in AdminUser, where: u.roles == ^[1], select: count(“*”))

How to get the count of rows with only “super”?

1. “root” ↩︎

Hi @rameshsharma , welcome to the forum!
Yours sounds like a different question than the one in this thread, as it’s not about LIKE/ILIKE queries (or am I mistaken?).

If so, it’s probably better to post it as a separate question, outlining your problem and goals. Some useful info would be:

• what is the type of the roles column? Is it a Postgres array of strings?
• Do you want to select rows that contain only super (and no other role), or rows that include super among the roles?

Hi Luca,

Thanks for responding. I posted here as I thought it could be linked to like search. S.orry for that
The roles column in database is a Postgres array of strings. You are correct.
I want to get the count of rows that include ‘super’ among the roles?

I don’t have a computer here to try this right now (I am on my phone now), but if you are selecting for rows that contain super among the roles, you should be able to do so with the ANY(array) expression. Adapting from your query, something like this (untested):

Repo.one(from u in AdminUser, where: fragment("ANY(roles)") == "super", select: count("*"))

1. “root” ↩︎

Sorry Luca, getting error here with this query:-

iex:9> Repo.one(from u in AdminUser, where: fragment(“ANY(roles)”) == “super”, select: count(“*”))

[debug] QUERY ERROR source=“admin_users” db=0.0ms queue=2.3ms idle=1758.9ms
SELECT count(‘*’) FROM “admin_users” AS a0 WHERE (ANY(roles) = ‘super’)
** (Postgrex.Error) ERROR 42601 (syntax_error) syntax error at or near “ANY”

query: SELECT count('*') FROM "admin_users" AS a0 WHERE (ANY(roles) = 'super')
(ecto_sql 3.3.3) lib/ecto/adapters/sql.ex:612: Ecto.Adapters.SQL.raise_sql_call_error/1
(ecto_sql 3.3.3) lib/ecto/adapters/sql.ex:545: Ecto.Adapters.SQL.execute/5
(ecto 3.3.2) lib/ecto/repo/queryable.ex:192: Ecto.Repo.Queryable.execute/4
(ecto 3.3.2) lib/ecto/repo/queryable.ex:17: Ecto.Repo.Queryable.all/3
(ecto 3.3.2) lib/ecto/repo/queryable.ex:112: Ecto.Repo.Queryable.one/3

Sorry, I am not able to check it now. Maybe you just need to add a space after ANY, like ANY (roles), or you need to invert the order, like "super" == fragment("ANY(roles)"), or maybe I am just completely off.

You are a genius Luca. Reverting the order works. Thanks for your help.

iex:13> Repo.one(from u in AdminUser, where: “super” == fragment(“ANY(roles)”), select: count(""))
[debug] QUERY OK source=“admin_users” db=1.7ms idle=1958.4ms
SELECT count('') FROM “admin_users” AS a0 WHERE (‘super’ = ANY(roles))
3.

Apparently one can pin the interpolated string too