I’m having trouble writing a query interpolating lists in my Ecto + Postgrex setup. In my database, I have tables named
Tag with a many-to-many association via an
ObjectTag table storing primary keys
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?