Ecto where like with list

Questions / Help
1

Hi, I’ve already known that we can use “where” with a list.

from(c in module, where: c.is_deleted == ^criteria)

( The criteria is a list. )

What I would like to know is how can I use a list for like.

What I expect is like …

from (c in query, where: like(c.name, ^search)
1 Like
2

You can’t, as AFAIK there is no such functionality in any SQL dialects I know. You would need to build such functionality on your own by using Enum.reduce.

1 Like
3

Ecto.Query.API.like/2:

Translates to the underlying SQL LIKE query, therefore its behaviour is dependent on the database.

PostgreSQL: LIKE

So unless I’m misunderstanding you - LIKE simply doesn’t work that way, it is only designed to match a string to a LIKE pattern.

What are you actually trying to do? There may be a different way of how to do it.

Even this example puzzles me - I’m not even sure this is supposed to work. There must be some unstated, non-typical assumption (e.g. c.is_deleted is an ARRAY rather than a scalar type) here to make this work (if it in fact does).

Lists are typically used with Ecto.Query.API.in/2:

from p in Post, where: p.id in [1, 2, 3]

where they naturally map to SQL (PostgreSQL IN comparison).

3 Likes
4

As @hauleth mentioned, you could accomplish this with a Enum.reduce/3 call:

query = select(Company, [c], c)

Enum.reduce(criteria, query, fn name, query ->
  where(query, [c], ilike(c.name, ^name))
end

That will create a series of AND predicates, if you need OR you can use or_where/3:

Enum.reduce(criteria, query, fn name, query ->
  or_where(query, [c], ilike(c.name, ^name))
end

I don’t know what your module really is, I just picked Company :grinning:

4 Likes
5

Hey! I’ve been trying to get this solution to work for me! To give some background on what I am trying to do. I would like to use this function to see which list of strings from the list of list of strings from the fruit column of my table, Food, contains a certain word.

criteria =  ((from f in Food, select: f.fruit) |> Repo.all)

this then returns

^criteria = [["apple", "banana", "oranges"], ["pink Apple", "red apple"], ["yellow banana" "green Banana"]]

I am hoping the Enum.reduce example you gave from using ilike could be a potential solution for this

Enum.reduce(criteria, query, fn fruit, query ->
  or_where(query, [f], ilike(f.fruit, ^"%apple%"))
end
6

The original poster’s question can be solved with a single clause - c.name LIKE ANY (ARRAY['pattern1%', 'pattern2%']) as a fragment.

Unfortunately, that doesn’t work with an array column on the left-hand side of LIKE ANY; PG complains about ERROR: operator does not exist: text[] ~~ text.

What raw SQL would you use to solve this problem?