How to include "None" in lte/gte comparisons?

Question

I've got this complex filtering mechanism...

d = copy(request.GET)
d.setdefault('sort_by', 'created')
d.setdefault('sort_dir', 'desc')
form = FilterShipmentForm(d)
filter = {
    'status': ShipmentStatuses.ACTIVE
}
exclude = {}
if not request.user.is_staff:
    filter['user__is_staff'] = False

if request.user.is_authenticated():
    exclude['user__blocked_by__blocked'] = request.user

if form.is_valid():
    d = form.cleaned_data
    if d.get('pickup_city'): filter['pickup_address__city__icontains'] = d['pickup_city']
    if d.get('dropoff_city'): filter['dropoff_address__city__icontains'] = d['dropoff_city']
    if d.get('pickup_province'): filter['pickup_address__province__exact'] = d['pickup_province']
    if d.get('dropoff_province'): filter['dropoff_address__province__exact'] = d['dropoff_province']
    if d.get('pickup_country'): filter['pickup_address__country__exact'] = d['pickup_country']
    if d.get('dropoff_country'): filter['dropoff_address__country__exact'] = d['dropoff_country']
    if d.get('min_price'): filter['target_price__gte'] = d['min_price']
    if d.get('max_price'): filter['target_price__lte'] = d['max_price']
    if d.get('min_distance'): filter['distance__gte'] = d['min_distance'] * 1000
    if d.get('max_distance'): filter['distance__lte'] = d['max_distance'] * 1000
    if d.get('available_on'): # <--- RELEVANT BIT HERE ---
        filter['pickup_earliest__lte'] = d['available_on'] # basically I want "lte OR none"
        filter['pickup_latest__gte'] = d['available_on']
    if d.get('shipper'): filter['user__username__iexact'] = d['shipper']

order = ife(d['sort_dir'] == 'desc', '-') + d['sort_by']

shipments = Shipment.objects.filter(**filter).exclude(**exclude).order_by(order) \
    .annotate(num_bids=Count('bids'), min_bid=Min('bids__amount'), max_bid=Max('bids__amount'))

And now my client tells me he wants pickup/drop-off dates to be 'flexible' as an option. So I've updated the DB to allow dates to be NULL for this purpose, but now the "available for pickup on" filter won't work as expected. It should include NULL/None dates. Is there an easy fix for this?

Answer 1

Don't think that's actually a django-specific question. Variable 'd' is a python dictionary, no? If so, you can use this:

filter['pickup_latest__gte'] = d.get('available_on', None)

Answer 2

Most database engines don't like relational comparisons with NULL values. Use <field>__isnull to explicitly check if a value is NULL in the database, but you'll need to use Q objects to OR the conditions together.

Answer 3

Flip the logic and use exclude(). What you really want to do is exclude any data that specifies a date that doesn't fit. If pickup_latest and pickup_earliest are NULL it shouldn't match the exclude query and wont be removed. Eg

exclude['pickup_latest__lt'] = d['available_on']
exclude['pickup_earliest__gt'] = d['available_on']