How do I express a NOT IN using ROM?

Issues
1

Hi folks,

new ROM user here. I’m trying to express the following SQL query using ROM:

SELECT id, name 
FROM rel WHERE ((rel.account = '...') AND rel.id NOT IN (SELECT id FROM ....))

The subselect returns a set of ids that I want to filter out of the result of the outer query, hence the NOT IN. I can’t seem to find a way to express this though.

A regular IN works fine, like this:

subselect = rel.select(....).query

full_query = rel.where(rel.account: ...).where { id.in(subselect) }

the above results in the correct SQL. However, when I try to negate somehow, it fails.

I’ve tried the not method:

full_query = rel.where(rel.account: ...).where { id.not(subselect) }

but that gets converted to !=, and gives this (invalid) SQL:

SELECT id, name 
FROM rel WHERE ((rel.account = '...') AND rel.id != (SELECT id FROM ....))

I’ve also tried prefixing with a !:

full_query = rel.where(rel.account: ...).where { !id.in(subselect) }

but that results in this SQL:

SELECT id, name 
FROM rel WHERE ((rel.account = '...') AND false)

Any tips? Am I overlooking something fundamental or will I have to revert back to using raw SQL to express this?

2

Of course, after banging my head against this for several days, I figure it out mere minutes after posting here :roll_eyes:

The trick is to use .exclude and stick the subselect arg in an array:

full_query = rel.where(rel.account: ...).exclude(id: [subselect])

I believe I actually tried this method at some point as well but didn’t use the array brackets around the subselect, which resulted in invalid SQL again. I’m not sure I follow the logic of sticking the subquery in an array but I won’t knock it too much: it works :slight_smile:

EDIT I also discovered that my original attempt at using not also works if I put the subselect in an array:

full_query = rel.where(rel.account: ...).where { id.not([subselect]) }
1 Like