Interpolating Lists into Ecto.Query.fragment/1

mindseyeblind

I’m having trouble writing a query interpolating lists in my Ecto + Postgrex setup. In my database, I have tables named Object and Tag with a many-to-many association via an ObjectTag table storing primary keys object_id and tag_id. The query I’m trying to write is to return Objects matching all tag_ids given to the function.

query =
  from(object in Object,
    select: object,
    where: object.category_id == ^category_id, # irrelevant to this question
    where:
      fragment(
        "EXISTS (SELECT NULL
                   FROM OBJECT_TAGS o_t
                   JOIN TAGS t ON t.id = o_t.tag_id
                  WHERE t.id IN (?)
                    AND o_t.object_id = ?
               GROUP BY o_t.object_id
                 HAVING COUNT(DISTINCT t.name) = ?)",
        ^tag_ids, # [1, 3, 5]
        object.id,
        ^tag_count # 3
      ),
    preload: [:tags, :category] # again, irrelevant to this question
  )

Repo.one(query)

This fails with the following error:

Postgrex expected an integer in -9223372036854775808..9223372036854775807, got [1, 3, 5]. Please make sure the value you are passing matches the definition in your table or in your query or convert the value accordingly.

How would I go about interpolating this list in the correct way? Alternatively, is there another way to approach this problem?

8 comments

#ecto

0 2970 8

2018-09-20 19:16:15 UTC

Showing Posts 1 to 8

Show Best Posts
Show All (oldest first)
Show All (newest first)

benwilson512

Author of Craft GraphQL APIs in Elixir with Absinthe

This isn’t really fragment specific, this is just a matter of how IN works with Postgres parameters. You need to instead do: WHERE t.id = ANY(?)

Post #1

mindseyeblind

This worked on resolving the error - with the upshot being that the query isn’t returning any packages, when it should be returning exactly 2 of them. Any idea what I’m doing wrong?

Post #2

OvermindDL1

Hard to tell without seeing your schema and complete SQL, but just as a note, wouldn’t a join be a lot more efficient then a repeated select per row to test the existence of something (then group by to ensure a single result if it can potentially join multiple)?

Post #3

mindseyeblind

I actually do have an implementation using a join - but I’d been advised that using an EXISTS lookup might be faster, so I was going to try benchmarking and comparing the two approaches.

Post #4

sanswork

Have you tried changing the NULL in your subquery to a 1?

Post #5

l00ker

I had this problem once before, and maybe there’s a better way to do this, but I’ll share how I solved it.

If you look closely at the error, Postgrex is telling you that instead of an integer type (or multiple integers separated by a comma), e.g. ... IN (1,2,3) it’s getting a list type, e.g. ... IN ([1,2,3]). So to fix this, you’ll need to convert the list into a binary e.g. "1,2,3" and modify your fragment SQL to have PostgreSQL convert the incoming binary to an array of integers using the PostgreSQL string_to_array() function and casting it’s that to an integer type.

Here’s your example fragment with the changes:

...
        fragment(
          "EXISTS (SELECT NULL
                     FROM OBJECT_TAGS o_t
                     JOIN TAGS t ON t.id = o_t.tag_id
                    WHERE t.id = ANY(string_to_array(?, ',')::integer[]))
                      AND o_t.object_id = ?
                 GROUP BY o_t.object_id
                   HAVING COUNT(DISTINCT t.name) = ?)",
           ^Enum.join(tag_ids, ","), # [1, 3, 5] -> "1,3,5"
...

Of course I didn’t test that code, but it should work. The Enum.join(tag_ids, ",") converts the list of integers into a binary e.g. “1,3,5” and that gets inserted into the string_to_array(?, ',')::integer[] in the fragment.

PostgreSQL gets what it needs and everyone is happy