How to construct dynamic Q() in Django-CodePudding

I'm trying to filter dynamic value from a form in template to my query in Django project

the value can be dynamic, but it would look like this

first_query = ['a', 'b', 'c']
second_query = ['d', 'e', 'f']

what i want to achieve is to query something like this

SELECT * FROM table WHERE column_1 = 'a' OR column_1 = 'b' OR column_1 = 'c' OR column_2 = 'd' OR column_2 = 'e' OR column_2 = 'f'

Here's what i've done

        first_query = [ Q(column_1__contains=w) for w in first_query ]

        """
        If you print first_query
        [<Q: (AND: ('column_1__contains', 'a'))>, <Q: (AND: ('column_1__contains', 'b'))>, 
          <Q: (AND: ('column_1__contains', 'c'))>]
        """


        reduce_first_query = reduce(or_, query_first_query)
        """
        (OR: ('column_1', 'a'), ('column_1', 'b'),('column_1', 'c'))
        """
        

        second_query = [ Q(column_2__contains=w) for w in second_query ]
        reduce_second_query = reduce(or_, query_second_query)

When i try to filter Item from my database it gives me rows more than doubled than what i do directly from the database

test_item = Item.objects.filter(reduce_first_query) # Gives 1900 ish rows, should be 900 ish

What do i do wrong, is it because of the way i construct Q() ? or there other mistake ?

NOTES

I'm using Postgresql 14

CodePudding user response：

To produce the query that you specified, you need in lookup, not contains:

first_query = ['a', 'b', 'c']
second_query = ['d', 'e', 'f']

qs = Item.objects.filter(Q(column1__in=first_query) | Q(columns2__in=second_query))

And btw, you can inspect the SQL query produced by ORM like this:

print(qs.query)

CodePudding user response：

To construct a dynamic Q object in Django, you can use the Q class and its methods to build the query condition dynamically.

For example, if you have a list of search keywords and you want to create a Q object that matches any of those keywords, you can use the Q.or_ method to combine multiple Q objects into a single query. Here is an example:

from django.db.models import Q
keywords = ['apple', 'banana', 'cherry']
q = Q()
for keyword in keywords:
    q |= Q(name__contains=keyword)
results = MyModel.objects.filter(q)

In this example, the q variable is initially set to an empty Q object. Then, a loop iterates over the list of keywords and uses the Q.or_ method to combine a new Q object for each keyword into the q object.

The Q object that is created for each keyword uses the name__contains keyword argument to match any MyModel objects that have a name field that contains the keyword.

At the end, the results variable will contain all MyModel objects that match any of the keywords in the list.

You can use the same technique to construct dynamic Q objects for other query conditions and combinations of conditions. The Q class provides many methods and operators that you can use to build complex query conditions dynamically.