CODEWITHSUNDEEP
pythonpostgresqlsqlalchemy
bard
bard

Reputation: 3052

SQLAlchemy filter for rows that do not contain value in an array column

I have 1001 rows in my table.

# 1001
print len(Session.query(MyTable).all())

One row has the string 'Recalled' in the info column, which is an array. 

# 1
query = Session.query(MyTable)
query = query.filter(MyTable.info.contains(['Recalled']))
print len(query.all())

But when I negate the filter, I get 0 results, instead of 1000 like I expected.

# 0
query = Session.query(MyTable)
query = query.filter(~MyTable.info.contains(['Recalled']))
print len(query.all())

Why isn't this working?

Upvotes: 4

Views: 9646

Answers (1)

Ian Wilson
Ian Wilson

Reputation: 9039

This is just NULL values being tricky. You should explicitly include them, like 

from sqlalchemy.sql import or_
query = Session.query(MyTable)
query = query.filter(or_(MyTable.info == None, ~MyTable.info.contains(['Recalled'])))
print len(query.all())

Upvotes: 8

Related Questions