CODEWITHSUNDEEP
postgresqlelixirphoenix-frameworkecto
skwidbreth
skwidbreth

Reputation: 8454

Phoenix/Ecto - query for matches in array of strings

In one of my Phoenix app's tables, I have a field that is an array of strings. I would like to be able to use a where: like() type of query to see if any of the values in the array contain a query string - however, I'm not sure how to do that. In a previous iteration of the app, the field in question was just a string field, and the following query worked perfectly:

results = from(u in User,
    where: like(u.fulltext, ^("%#{search_string}%"))
    |> Repo.all()

Now that I have changed the fulltext field into an array of strings (character varying(255)[], in Postgres terms), this query understandably fails with the error

ERROR 42883 (undefined_function): operator does not exist: character varying[] ~~ unknown

but I'm not sure how I might refine the query to match the new schema.

For example, a user's fulltext field will look like

["john smith", "[email protected]"]

and the associated record should be returned when search_string is "john" or "@test" or "n smith", etc. - if the search_string matches any part of either of the list values.

In plain English, the query would read something like "return records where a value like search_string is found in the list u.fulltext".

I can think of various 'hacky' workarounds, like just returning the list of all users and then using some chained Enum.map functions to run through them and check the values of fulltext for partial matches, but if there's a more elegant solution using Ecto's query syntax, I would much rather opt for that. Can anyone offer any guidance?

Upvotes: 0

Views: 4478

Answers (2)

Mike Buhot
Mike Buhot

Reputation: 4885

You can use a fragment and unnest to convert the array to a join:

user_texts = 
  from u in User, 
  select: %{id: u.id, fulltext: fragment("unnest(fulltext)")}

query = 
  from u in User, 
  join: t in subquery(user_texts), on: u.id == t.id, 
  where: like(t.fulltext, ^("%#{search_string}%")), 
  select: u,
  distinct: true

Repo.all(query)

Upvotes: 2

Dogbert
Dogbert

Reputation: 222348

You can use unnest in PostgreSQL with a subquery to check if any item of an array is LIKE something:

from(p in Post, select: p.tags, where: fragment("exists (select * from unnest(?) tag where tag like ?)", p.tags, "%o%")

In your case, this should work:

from(u in User, where: fragment("exists (select * from unnest(?) tag where tag like ?)", u.fulltext, ^("%#{search_string}%"))

iex(1)> Repo.insert! %Post{tags: ~w(foo bar baz)}                                                                              [debug] QUERY OK db=0.3ms
iex(2)> Repo.insert! %Post{tags: ~w(quux)}
iex(3)> Repo.insert! %Post{tags: ~w(hello world)}
iex(4)> query = "%o%"
"%o%"
iex(5)> Repo.all from(p in Post, select: p.tags, where: fragment("exists (select * from unnest(?) tag where tag like ?)", p.tags, "%o%"))
[debug] QUERY OK source="posts" db=3.9ms
SELECT p0."tags" FROM "posts" AS p0 WHERE (exists (select * from unnest(p0."tags") tag where tag like '%o%')) []
[["foo", "bar", "baz"], ["hello", "world"]]

Upvotes: 4

Related Questions