Ecto query using like/ilike in query

Questions / Help
1

Good day to you all.

I have been struggling to get a query involving like and ilike to work.
Can anyone assist me on this, please?

product="SurfacePro"

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

Have you taken a look at the documentation? https://hexdocs.pm/ecto/Ecto.Query.API.html#like/2

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
12 Likes
3

Hello @NobbZ,

Many thanks.
Your suggestion worked.

I had tried several different options without success.

Regards,

Jerry

1 Like
4

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 == ^[“root”], select: count("*"))

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

5

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?
6

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?

1 Like
7

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” ↩︎

8

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
9

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.

10

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.

1 Like
11

Apparently one can pin the interpolated string too :slight_smile:

1 Like