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 th开发者_高级运维e "available for pickup on" filter won't work as expected. It should include NULL/None dates. Is there an easy fix for this?
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']
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.
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)
精彩评论