[PYTHON] Do an ambiguous search for mysql in Django

About fuzzy search of mysql

https://www.softel.co.jp/blogs/tech/archives/1877 For the query, I referred to here.

How to write in Django

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)

How to use

#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'])

Be careful when join is required

#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)

Impressions

Even if I know how to write it in a query, I'm always wondering how to write it in Django