How to query by column == None

[ricardo8990]

I've seen in #71 that there is a rom.NOT_NULL. However, from the code I can see that it only works for OneToOne or ManyToOne columns.

Is there a way to query for any other column type?

In my case I'm trying to add a column deactivated_at of type DateTime. I want to filter all active items by querying when deactivated_at doesn't exists or current time it's lower than deactivated_at value. But I still can't get it done.

class Key(Model):
    ...
    deactivated_at = DateTime(index=True)

active_keys = Key.query.filter(deactivated_at=(dt.datetime.utcnow() + dt.timedelta(microseconds=1), None))

Thanks in advance

[josiahcarlson]

1. Don't use a default of None.
2. Use a default of some "epoch"

I like January 1, 1970, because it's a convenient DEFAULT_MISSING_DATE = datetime.datetime.utcfromtimestamp(0) global define (used via DateTime(index=True, default=DEFAULT_MISSING_DATE). But you can pick any arbitrary date that DateTime supports and is before your valid data range.

[ricardo8990]

I get there are other ways to implement it. But I think would be a good feature request to be able to query if a column is not None. There're pretty common use cases.
In the case of SqlAlchemy you're able to query like: filter(deactivated_at!=None)

[josiahcarlson]

The problem: how to represent None in an inverted index, when historically we've not stored it, because None is missing data?

I will add an index_None=False option for columns, and if it is true, I will keep a separate "these entities store None" sets for every column. To ensure that this doesn't break folks weirdly, we'll allow duplicate None values in the case where index_None=True, unique=True.

That should solve your problem, and hopefully I'll get to it some time in the next week or two.