CODEWITHSUNDEEP
postgresqlknex.js
Louis Sankey
Louis Sankey

Reputation: 492

Postgres with Knex - query where an array contains a particular value

Surprising as it is, I haven't been able to figure out how to return a table that has an array that contains a particular value using knex. I see in the Postgres docs it's possible.

in sql

SELECT ... FROM fdt WHERE c1 IN (1, 2, 3)

but I can't find how this would be done with knex. One suggestion online was using whereIn.

So I tried

knex.from('pin_table').select('*').whereIn('likes_recieved', id)
 .whereIn('likes_sent', id)

(I also tried whereIn with the array and search parameter switched like .whereIn(id, 'likes_recieved')

but I keep getting back a syntax error:

"select * from "pin_table" where "10" in $1 and "10" in $2 - syntax error at or near "$1"

Can anyone tell me how to do this with knex or knex.raw?

Thanks

Upvotes: 3

Views: 7063

Answers (1)

Rich Churcher
Rich Churcher

Reputation: 7654

This depends a bit on whether you're using the ARRAY type, or if likes_sent is built from a subquery etc. If it's an array, you're probably going to need to use ANY:

knex.from('pin_table').whereRaw('? = ANY(likes_received)', id);

That's a best guess without seeing your schema. Usually I'd be tempted to use a separate table rather than the Postgres array:

knex('pin_table as p')
  .join('likes as l', 'p.id', 'l.pin_id')
  .where('l.user_id', id);

See https://stackoverflow.com/a/20220450/122643 for a discussion of potential pitfalls and advantages of Postgres arrays.

Upvotes: 5

Related Questions