Ecto IN to match substring

Questions / Help
1

Trying to match a partial string against an array of full strings. Here’s where I’m stuck:

full_names = ["Larry King", "Jessica Jackson", "Billy Idol", "Ray Charles"]
from(p in Person, where: p.first_name in ^full_names) |> Repo.all

First name wont match because it’s looking for an exact match. How can I go about this?

Been messing with ARRAY_TO_STRING with an example like this as raw sql but haven’t gotten anything to work.

SELECT first_name
  FROM people
  WHERE first_name SIMILAR TO ARRAY_TO_STRING((SELECT full_name FROM leaders), ',')
2

You’ll have to use Fragment

3

Heres what I came up with.

# Convert list of full names to a list of first names
full_names = ["Larry King", "Jessica Jackson", "Billy Idol", "Ray Charles"]
first_names = 
  Enum.map(full_names, fn full_name -> 
    full_name
    |> String.split(" ")
    |> List.first()
  end)

# Dynamically return an ecto `or/2` statement based on how many entries in your list
# in our case 4
conditions = Enum.reduce(first_names, false, fn name, acc ->
  dynamic([d], d.first_name == ^name or ^acc)
end)

# Query that returns all matches
matches =
  from(d in leaders,
    select: d.first_name,
    where: ^conditions
    )
  |> Repo.all()

The example doesn’t exactly correspond but the idea is the same. Struggled a lot with this one but I learned how to use Ecto.Query.dynamic/2 with Enum.reduce/3 and there’s a ton of potential there.

# The query builds as
Ecto.Query<from d0 in leaders,
 where: d0.first_name == ^"Larry" or (d0.first_name == ^"Jessica" or (d0.first_name == ^"Billy" or (d0.first_name == ^"Ray" or ^false))),
 select: d0.first_name>
2 Likes