Multiple field boolean filtering by value with Django

Question

I have 3 fields in a dataset that I want to create querysets over the possible permutation values for those fields. These fields are based on a numerical rating scale, so the queries are based on whether a value is matched in the given field.

I've already filtered the fields to ensure that the values in all three fields are at minimum a 7 (out of the possible integer values of 7, 8, 9 for those three fields).

Now I want to find the following potential comparisons as querysets (!7 means the value can be 8 or 9):

[7, 7, 7]

[7, !7, 7]

[7, 7, !7]

[!7, 7, 7]

[7, !7, !7]

[!7, 7, !7]

[!7, !7, 7]

I was able to do this with pandas by creating comparison columns with boolean evaluation to check the following permutations, (i.e. column 1 = 7, column 2 != 7 (so either 8 or 9), column 3 != 7 (so either 8 or 9)).

permutation_dict = {
    "all": (True, True, True),
    "one and three": (True, False, True),
    "one and two": (True, True, False),
    "two and three": (False, True, True),
    "one": (True, False, False),
    "two": (False, True, False),
    "three": (False, False, True),
}

This permutation dictionary is then looped over to create a comparison column which I could then get the count of "Trues" for a given permutation dictionary entry.

df = pd.DataFrame(queryset.values_list(
                        "one",
                        "two",
                        "three"
                    )
                )
bool_df = df.apply(lambda x: x == 7 if x.name in [0, 1, 2] else x)
v = permutation_dict["all"]              
comparison_column = bool_df[
                        (bool_df[0] == v[0])
                        & (bool_df[1] == v[1])
                        & (bool_df[2] == v[2])]

Is there a way to do a similar operation with django filters? While I get that I could chain queryset.filter(one=7).filter(two!=7).filter(three(!=7) to replicate permutation_dict["one"], it seems like I would have to hardcode the filter expression (eq vs neq) to get the same result.

Answer 1

You can use Q:

A Q object (django.db.models.Q) is an object used to encapsulate a collection of keyword arguments. These keyword arguments are specified as in “Field lookups” above.

# create initial data for the sample
M1.objects.create(i1 = 7, i2 = 7, i3 =7)
M1.objects.create(i1 = 7, i2 = 8, i3 =7)
M1.objects.create(i1 = 7, i2 = 9, i3 =7)

# importing Q
from django.db.models import Q

# Encapsulating:
q1 = Q(i1 = 7)
q2 = Q(i2 = 7)
q3 = Q(i3 = 7)

# Permutation dict, complex lookups:
permutation_dict = {
    "all": q1 & q2 & q3,
    "one and three": q1 & ~q2 & q3,
    # ...
}

# Counting
M1.objects.filter( permutation_dict["all"] ).count()
# return 1

M1.objects.filter( permutation_dict["one and three"] ).count()
# returns 2

I don't know if this is exactly what you are looking for, but I'm pretty sure this example will help you find the way.