Django query set filter reverse startswith on charfield-CodePudding

Image some kind of product-rule which has 2 conditions:

name are equal
sku's have partial match, starts with.

The rule model looks like this:

class CreateAndAssignRule(models.Model):
    name_equals = models.CharField(max_length=100)
    sku_starts_with = models.CharField(max_length=100

Now I want to fetch all of the rules with name Product 1 and match sku sku-b-292

class CreateAndAssignRuleQuerySet(QuerySet):
    def filter_by_name_and_sku(self, name, sku):
        # We're looking which of the rules have a matching name, and where the rule have a string which is the string of the sku provided.
        rules = self.filter(name_equals=name)

        approved_ids = []
        for rule in rules:
            # We're looping through the rules to find out which of them has the beginnings of the sku.
            # a sku_starts_with field would contains value eg: 'sku-a' where as the search string would be the full sku 'sku-a-111'.  We want to match 'sku-a-111' but not 'sku-b-222'.
            if sku.startswith(rule.sku_starts_with):
                approved.append(rule.id)

        return self.filter(id__in=approved_ids)

although the above works, it's hardly efficient especially as the number of rule is starting to grow a lot.

How can I resolve this with a queryset? Filtering on __startswith doesn't do the trick as it the reverse.

CodePudding user response：

Filter with:

from django.db.models import F, Value

class CreateAndAssignRuleQuerySet(QuerySet):
    def filter_by_name_and_sku(self, name, sku):
        return self.alias(
            sku=Value(sku)
        ).filter(
            name_equals=name,
            sku__startswith=F('sku_starts_with')
        )

We thus here inject the sku in the queryset, and then use this to work with a __startswith lookup ^[Django-doc].