Interpolating Lists into Ecto.Query.fragment/1

Questions / Help
1

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?

2

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(?)

1 Like
3

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?

4

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)?

2 Likes
5

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.

6

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

7

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 :slight_smile:

8

You can make this even easier:

WHERE t.id = ANY(?)

With this you can avoid the Enum.join and just have ^tag_ids

2 Likes
9

Cool. I see that now. Thanks!