Ecto - performing a case insensitive search with a list

Questions / Help
,
1

I’m attempting to search my database for all users with an e-mail contained in a given list of e-mails, irrelevant of case. My code is:


  def get_user_ids_from_emails(emails) do
    emails = Enum.map(emails, fn email -> String.downcase(email) end)

    from(
      u in User,
      where:
        fragment(
          "LOWER(?) IN ?",
          u.email,
          ^emails
        ),
      select: u.id
    )
  end

I’m having to do it this way as I believe ilike only applies to strings, not lists. When I try and execute this i receive

** (Postgrex.Error) ERROR 42601 (syntax_error) syntax error at or near "$1"
     
         query: SELECT u0."id" FROM "user" AS u0 WHERE (LOWER(u0."email") IN $1)

Is this the right approach?

And if so how can I format the list so that it’s accepted by SQL?

2

It should work if you only use LOWER() in the fragment like this:

def get_user_ids_from_emails(emails) do
    emails = Enum.map(emails, fn email -> String.downcase(email) end)

    from(
      u in User,
      where:
        fragment("LOWER(?)", u.email) in ^emails,
      select: u.id
    )
  end
2 Likes
3

D’oh - many thanks! Works perfectly