https://www.softel.co.jp/blogs/tech/archives/1877 For the query, I referred to here.
Use ʻextra because special queries cannot be done with
filter`
def get_ambiguous_queryset(self, queryset, value, columns):
"""
Returns a queryset for ambiguous search
:param queryset:
:param value:
:param columns:
:return:
"""
query_text = ''
count = 0
for column in columns:
if query_text != '':
query_text += ' OR '
temp_value = value
if isinstance(value, list):
if len(value) > count:
temp_value = value[count]
else:
temp_value = value[0]
query_text += f"CONVERT({column} USING utf8) COLLATE utf8_unicode_ci LIKE %s"
temp_value = temp_value.replace('%', r'\%%').replace('_', r'\_')
params.append(f'%{temp_value}%')
count += 1
if query_add:
query_text += query_add
return queryset.extra(where=[query_text], params=params)
#Pass queryset, the value you want to search, and the column to search
self.get_ambiguous_queryset(queryset, value, ['name', 'code', 'description'])
#You can also pass the value you want to search in an array(It is linked with the column to be searched)
self.get_ambiguous_queryset(queryset, [value, self.convert_tel(value)], ['name', 'tel'])
#NG pattern (shop is not joined and an error occurs)
self.get_ambiguous_queryset(queryset, value, ['shop.name', 'shop.code'])
#OK pattern (join if shop is written in filter)
self.get_ambiguous_queryset(queryset, value, ['shop.name', 'shop.code']).filter(shop__name__isnull=False)
Even if I know how to write it in a query, I'm always wondering how to write it in Django
Recommended Posts