Query for "not null" values

[tflorac]

Hi, I'm using an Hypatia catalog in a Pyramid project using ZODB. I have an index storing datetime values. How can I create query on this index to get results where value is null or higher than a given value?

Best regards, Thierry

[vincentfretin]

Hi,

The nice thing you have with hypatia FieldIndex is that you can retrieve the not indexed docs with not_indexed() method. Here is some code I did 2 years ago.

class InRangeWithNotIndexed(InRange):                                           
    """ Index value falls within a range, or index value is None.
                                                                                
    CQE eqivalent: lower < index < upper                                        
                   lower <= index <= upper                                      
    """                                                                         
                                                                                
    def _apply(self, names):                                                    
        result = super(InRangeWithNotIndexed, self)._apply(names)               
        # result is a BTrees.LFBTree.LFSet                                      
        # and self.index.not_indexed() is a BTrees.LFBTree.LFTreeSet            
        result = self.index.family.IF.union(result, self.index.not_indexed())   
        # result.update(self.index.not_indexed()) is slower                     
        # return a BTrees.LFBTree.LFSet                                         
        return result

Then you can use this in your query like this:

your_index = yourcatalog['yourindex']
query = query & InRangeWithNotIndexed(your_index, start, end, excludemin=False, excludemax=False)

[tflorac]

Here is another option to just check for "None" values; we can combine this comparator with any query operator:

class IsNone(Comparator):
    """Index query search for None values"""

    def __init__(self, index):
        super().__init__(index, None)

    def _apply(self, name):
        return self.index.not_indexed()


query = Or(Ge(catalog['expiration_date'], datetime.now(timezone.utc)),
           IsNone(catalog['expiration_date']))