Ecto query field where array contains a value

Questions / Help
1

I have a field in my schema that is {:array, :string} and I want a query that returns the id if that field has an array that contains a specific term. I thought I figured it out based on:

and
https://hexdocs.pm/ecto/Ecto.Query.API.html#any/1

but I can’t seem to create the appropriate Ecto query. Basically I want to translate

select * from mytable where search_term=ANY(search_field);
2

This is what I’m doing now and it compiles but not sure if it is going to actually do what I want.

from q in MyTable,
where: like(fragment("ARRAY_TO_STRING(?, ',')", q.search_field), ^search_term)

Thoughts/suggestions?

3

The ARRAY_TO_STRING query isn’t going to be able to use an index, so it’s going to be expensive if the table is large.

Does writing my_table.search_term == fragment("ANY(?)", my_table.search_field) in a where do what you’re looking for?

1 Like
4

Yes, I think that works better. Thank you!

5

Doesn’t in work?

from q in query, where: ^search_term in q.field
3 Likes
6

I just had a similar issue, yes this should work.