1

record_list is a Python list containing a list of keywords to be searched. The following code is my Django views.py where I am searching the MySQL database using these keywords. The code searches in columns product_name and description and returns the entire record where the keywords give search hits.

q_object = Q(product_name__icontains=record_list[0]) | Q(description__icontains=record_list[0])
for item in record_list:
   q_object.add(Q(product_name__icontains=item) & (Q(description__icontains=item)), q_object.connector)
    
queryset = Products.objects.filter(q_object).values().distinct()
    
for query in queryset:
   Filtered.objects.create(**query)

The search result is successfully saved in the queryset and I am able to save the queryset in database table. But I also want also to save the keywords that gave search hits in the 'queryset' along with the respective search results. Is there a way to do so using Django Q objects?

hynekcer
  • 14,942
  • 6
  • 61
  • 99
User
  • 11
  • 1
  • You wrote probably an incorrect operator '&' and a superfluous parenthesis in the expression `& (Q(description__icontains=item)), ` that doesn't match the expression for the first item of record_list. – hynekcer Jun 22 '20 at 07:32

1 Answers1

0

I recommend to evaluate only an equivalent of Q() expressions by Python. Finally I will explain why.

It is expected that the model Filtered has a CharField keywords and keywords are separated by a character, that is never a part of keyword, e.g. a space or a comma.

q_object = ~Q()  # trick: a filter with empty results
for item in record_list:
   q_object |= Q(product_name__icontains=item) | (Q(description__icontains=item))

queryset = Products.objects.filter(q_object).values().distinct()

new_items = []
for item_dict in queryset:
    used_keywords = [keyword for keyword in record_list
                     if keyword.lower() in item_dict['product_name'].lower() or
                     if keyword.lower() in item_dict['description'].lower()]
    new_items.append(Filtered(keywords=used_keywords, **item_dict))
Filtered.objects.bulk_create(new_items)

Notes to the code:

  • The last part of your code is optimized by bulk_create() method instead of simple create().

  • The explicit OR connector ('|=') is better than an indirect connector q_object.connector that must by finally recognized as OR after reading more code.

  • It is not useful to combine .values() and .distinct() if you don't use joins, because the primary key field is also a part of values() fields and the primary key is unique in a queryset based only on one table. I didn't remove it, because maybe a join is hidden in a record_list that could be traversing by more tables.

Why the matching keywords can not be evaluated directly by Q objects? because WhereNode can not be evaluated no way neither directly in Python without executing them individually by SQL or they can not be a part of SELECT clause in SQL. Q expressions are very general and extensible e.g. by user defined functions with many variables. Therefore a Python evaluation doesn't make sense to be implemented in Django only for simple types of expressions. Evaluating by SQL for every keyword would be ineffective, but it can be still useful for running tests that the results of SQL and Python implementation are the same for some set of test cases or in debug mode, especially if you will later edit these Q expressions.

hynekcer
  • 14,942
  • 6
  • 61
  • 99