How to run a SELECT WHERE column IN (...) query?

Questions / Help
,
1

Hi all!

I don’t know what to do to make Ecto happy to run the following query:

 sql = "" <>
   "SELECT chat_user_notification_keys.key " <>
   "FROM chat_user_rooms " <>
   "JOIN chat_user_notification_keys ON chat_user_notification_keys.user_id = chat_user_rooms.user_id " <>
   "JOIN users                       ON users.id = chat_user_rooms.user_id " <>
   "JOIN chat_rooms                  ON chat_rooms.id = chat_user_rooms.room_id " <>
   "WHERE chat_rooms.id_ref = $1::text::uuid " <>
   "  AND users.slug NOT IN ($2)"

 case Ecto.Adapters.SQL.query(BusChatWeb.Repo, sql, [room_id, user_slugs_who_are_present]) do
   {:ok, %{num_rows: num_keys, rows: keys}} ->
     case num_keys do
       0 -> # ...
       _ -> # ...
     end
 end

Currently, I get the following error message:

** (ArgumentError) Postgrex expected a binary, got []. Please make sure the value you are passing matches the definition in your table or in your query or convert the value accordingly.
[debug] QUERY ERROR db=9.9ms
SELECT chat_user_notification_keys.key FROM chat_user_rooms JOIN chat_user_notification_keys ON chat_user_notification_keys.user_id = chat_user_rooms.user_id JOIN users                       ON users.id = chat_user_rooms.user_id JOIN chat_rooms                  ON    chat_rooms.id = chat_user_rooms.room_id WHERE chat_rooms.id_ref = $1::text::uuid   AND users.slug NOT IN ($2) ["afe13f89-7819-43d9-9bb8-b4ba38a2ddac", []]
            (ecto) /Users/francois/Projects/chat_web/deps/postgrex/lib/postgrex/type_module.ex:717: Ecto.Adapters.Postgres.TypeModule.encode_params/3
        (postgrex) lib/postgrex/query.ex:45: DBConnection.Query.Postgrex.Query.encode/3
    (db_connection) lib/db_connection.ex:1071: DBConnection.describe_run/5
    (db_connection) lib/db_connection.ex:1142: anonymous fn/4 in DBConnection.run_meter/5
    (db_connection) lib/db_connection.ex:1199: DBConnection.run_begin/3
    (db_connection) lib/db_connection.ex:584: DBConnection.prepare_execute/4
            (ecto) lib/ecto/adapters/postgres/connection.ex:93: Ecto.Adapters.Postgres.Connection.execute/4
            (ecto) lib/ecto/adapters/sql.ex:243: Ecto.Adapters.SQL.sql_call/6
        (chat_web) lib/chat_web/repo.ex:117: ChatWeb.Repo.find_notifiable_keys/2

The problem is related to the $2 parameter: it’s a list in Elixir-land. How can I convert/cast a list to something Ecto will be happy?

Thanks!

2

I tried using the Ecto.Query API, like this:

  query = from chat_user_rooms in "chat_user_rooms",
    join:   chat_user_notification_keys in "chat_user_notification_keys",
    where:  chat_user_notification_keys.user_id == chat_user_rooms.user_id,
    join:   users in "users",
    where:  users.id == chat_user_rooms.user_id,
    join:   chat_rooms in "chat_rooms",
    where:  chat_rooms.id == chat_user_rooms.room_id,
    where:  chat_rooms.id_ref == type(^room_id, :uuid),
    where:  users.slug in ^user_slugs_who_are_present,
    select: chat_user_notification_keys.key

This fails because room_id is a string, but the database type is a UUID. The above fails with:

** (UndefinedFunctionError) function :uuid.cast/1 is undefined (module :uuid is not available)
             :uuid.cast("afe13f89-7819-43d9-9bb8-b4ba38a2ddac")

If I remove the type cast, then Postgrex complains with:

** (ArgumentError) Postgrex expected a binary of 16 bytes, got "afe13f89-7819-43d9-9bb8-b4ba38a2ddac". Please make sure the value you are passing matches the definition in your table or in your query or convert the value accordingly.

Note that the above query is not even the same query I wrote originally. I used NOT IN, but this query is IN. I have to find how to do a NOT IN now.

Thanks!

3

not users.slug in ^user_slugs_who_are_present, the not is a unary function, the inis a binary function, thus the precedence is likenot(users.slug in ^user_slugs_who_are_present)`. :slight_smile:

I think a future-elixir version is going to allow not in as a binary function though, I think…

Strings are not UUID’s. Something like "afe13f89-7819-43d9-9bb8-b4ba38a2ddac" is not a UUID but is a string. A UUID is 16 bytes of binary. The "afe13f89-7819-43d9-9bb8-b4ba38a2ddac" is a common human-readable representation but you should never ever ever pass around UUID’s in the human readable representation and should keep them as what they are, 16-byte binaries (in my humble opinion ^.^). However yes, you need to cast it with I think it is Ecto.UUID.cast, not :uuid. :slight_smile:

4

Thanks @OvermindDL1. The UUIDs are coming straight from the UI layer. I kept them as strings, because that makes it easy to inspect the value from anywhere in the stack.

I finally found a solution to my problem by reusing the ANY operator. My original SQL query is now:

-- as before
  AND NOT users.slug = ANY ($2::text[])

This works and satisfies me.

Thanks!

1 Like
5

You need to dump the string UUID first to a binary like this:

{:ok, bin} = Ecto.UUID.load(room_id)

Now you can use the binary UUID directly in your query like this:

where: chat_rooms.id_ref == ^bin

Note that if you use an Ecto schema, you don’t need to do all this manual casting and dumping.

1 Like
6

@zambai, {:ok, bin} = Ecto.UUID.load(room_id) is casting. I still have to do it somewhere. The schema isn’t free: the cost has to be paid somewhere.

7

I’m not sure if I understand what your meaning. Are you worried about the performance overhead of Ecto?

8

No, you said “you don’t need to do all this manual casting and dumping”. I only pointed out that it had to be done somewhere, either at the point of the query, or before, in the controller layer. HTTP only speaks strings. Casting will merely be done somewhere else.

9

What @zambal is saying is that if you had a schema, YOUR code wouldn’t need to worry about casting at all, Ecto would know what type it should be and will cast for you.