CODEWITHSUNDEEP
django
mpen
mpen

Reputation: 283213

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

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?

Upvotes: 3

Views: 4439

Answers (3)

Will Hardy
Will Hardy

Reputation: 14846

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']

Upvotes: 19

Ignacio Vazquez-Abrams
Ignacio Vazquez-Abrams

Reputation: 799230

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.

Upvotes: 5

jonesy
jonesy

Reputation: 3542

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)

Upvotes: 0

Related Questions