Django filter __lte __gte strings-CodePudding

I would like to filter models given a user input. For example the user would like to go on a cruise that is 1-5 days long. In the template I have a selector with these values (1-5, 6-9, 10-16, 17 ). In the view I create these as kwargs.

def create_kwargs(from_date, nights):
    kwargs = {}
    got_nights = nights != '-'
    got_date = False if not from_date else True

    if got_nights and (not got_date):
        nights_kwargs(kwargs, nights)
    if got_nights and got_date:
        nights_kwargs(kwargs, nights)
        kwargs['start_months__contains'] = from_date
    if (not got_nights) and got_date:
        kwargs['start_months__contains'] = from_date
    return kwargs


def nights_kwargs(kwargs, nights):
    if '-' in nights:
        c_min, c_max = nights.split('-')
        kwargs['cruise_duration__gte'] = c_min
        kwargs['cruise_duration__lte'] = c_max
    else:
        kwargs['cruise_duration__gte'] = '17'

Than I feed these kwargs to the filter method:

for i, area in enumerate(filter_areas):
    cruises = GeneralCruise.objects.filter(areas__contains=area, **kwargs)

Previously I tried to check equality and it worked:

kwargs['cruise_duration'] = '1'

My problem is that if I write __lte or __gte it returns all the models even if they are not matching the criteria. I read other questions regarding this and I think this should be working.

This is the model:

class GeneralCruise(models.Model):
    company = models.CharField(max_length=255, null=True)
    ship_name = models.CharField(max_length=255, null=True)
    ship_img = models.CharField(max_length=255, null=True)
    cruise_name = models.CharField(max_length=255, null=True)
    arrival = models.CharField(max_length=255, null=True)
    departure = models.CharField(max_length=255, null=True)
    cruise_duration = models.CharField(max_length=255, null=True)
    start_dates = models.JSONField(default=list)
    start_months = models.JSONField(default=list)
    cabins = models.JSONField(default=list)
    days_desc = models.JSONField(default=list)
    port_codes = models.JSONField(default=list)
    areas = models.JSONField(default=list)

CodePudding user response：

cruise_duration is a CharField, that means that it will not order by value, but lexicographically. For example '10' < '9' lexicographically, since the first characters are '1' and '9', and '1' is first in the alphabet.

The most elegant solution is to make it a numerical field, like an IntegerField:

class GeneralCruise(models.Model):
    # …,
    cruise_duration = models.IntegerField(null=True)
    # …

This will likely result in some trouble with migrations where probably the most effective way to get rid of it is to remove all the migrations, and thus start over with a fresh database.

If that is not an option, casting it to an IntegerField is probably the most effective way.

In that case the queryset looks like:

from django.db.models.functions import Cast, IntegerField

GeneralCruise.objects.annotate(
    cruise_duration_int=Cast('cruise_duration', output_field=IntegerField())
)

and then filter with cruise_duration_int__lte=… and cruise_duration_int__gte=…. But this is not an elegant solution.

It is also a bit odd that your model only has CharFields and JSONFields. Usually one uses ForeignKeys to other models to avoid data duplication, and get the database in a database normal form.

CodePudding user response：

The problem you're having is that your cruise_duration field is a CharField so gte and lte don't behave how you would expect (say, if it were a IntegerField). You're comparing strings rather than numerical values.

To illustrate the difference:

>>> "9" > "10"
True
>>> 9 > 10
False

You'll want to change your cruise_duration column type to a field type that can be compared based on cardinal value (like an IntegerField).

Accordingly, the inputs from your kwargs will also need to be sanitized and converted to integers when provided as the __lte/__gte arguments for filtering the model on that field.