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

Question

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

Rich Churcher · Accepted Answer · 2021-01-18 21:45:32Z

5

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.

edited Jan 18, 2021 at 21:45

answered Jan 18, 2021 at 0:15

Rich Churcher

7,6923 gold badges40 silver badges61 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Louis Sankey Over a year ago

Looks like that worked, and good tip about the separate table.. I'll keep that in mind in the future.

Collectives™ on Stack Overflow

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

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related