CODEWITHSUNDEEP
djangodjango-querysetdjango-filter
Sachi Tekina
Sachi Tekina

Reputation: 1810

Django filter using Q and multiple fields with different values

I am trying to generate a result that satisfies with the filter query below:

indicators = request.GET.getlist('indicators[]')
fmrprofiles = FMRPriority.objects.all()
q_objects = Q()
obj_filters = []
for indicator in indicators:
    split_i = indicator.split('_')
    if len(split_i) == 5:
        if not any(d['indicator'] ==  split_i[1] for d in obj_filters):
            obj_filters.append({
                'indicator': split_i[1],
                'scores': []
            })

        for o in obj_filters:
            if split_i[1] == o['indicator']:
                o['scores'].append(int(split_i[4]))

for obj in obj_filters:
    print (obj['scores'])
    q_objects.add(Q(pcindicator__id = int(obj['indicator'])) & Q(score__in=obj['scores']), Q.AND)
print (q_objects)
fmrprofiles = fmrprofiles.values('fmr__id','fmr__road_name').filter(q_objects).order_by('-fmr__date_validated')
print (fmrprofiles.query)

Basically, indicators is a list e.g. ['indicator_1_scoring_1_5', 'indicator_1_scoring_1_4', 'indicator_2_scoring_2_5']

I wanted to filter FMRPriority with these following fields:

e.g. pcindicator is equal 1 and scores selected are 5,4..another selection pcindicator is equal to 2 and scores selected are 3. The query q_objects.add(Q(pcindicator__id = int(obj['indicator'])) & Q(score__in=obj['scores']), Q.AND) returns empty set..i have tried also the raw sql, same result.

Model:

class FMRPriority(models.Model):

    fmr = models.ForeignKey(FMRProfile, verbose_name=_("FMR Project"), on_delete=models.CASCADE)
    pcindicator = models.ForeignKey(PCIndicator, verbose_name=_("Priority Indicator"), on_delete=models.PROTECT)
    score = models.FloatField(_("Score"))

Upvotes: 1

Views: 1518

Answers (1)

Sachi Tekina
Sachi Tekina

Reputation: 1810

I solve this by using OR and count the occurrence of id then exclude those are not equal to the length of filters:

for obj in obj_filters:
    print (obj['scores'])
    q_objects.add(
        (Q(fmrpriority__pcindicator__id = int(obj['indicator'])) & Q(fmrpriority__score__in=obj['scores'])), Q.OR
    )

fmrprofiles = fmrprofiles.values(*vals_to_display).filter(q_objects).annotate(
        num_ins=Count('id'),
        ...
    )).exclude(
        ~Q(num_ins = len(obj_filters))
    ).order_by('rank','road_name')

Upvotes: 1

Related Questions