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

Recommended Posts

Do an ambiguous search for mysql in Django
Search for strings in Python
Search for strings in files
Loop the For statement in reverse in an HTML file on Django
django search
Same-Site attribute setting for cookies in Django
(Note) Template file search order in Django
Get query parameters for GET requests in Django
Django search
How to do Server-Sent Events in Django
Search for yourself from methods in Django's model
[Introduction for beginners] Working with MySQL in Python
Django 1.9 for internationalization
Forms in Django
Procedure for creating an application with Django with Pycharm ~ Preparation ~
Build an interactive environment for machine learning in Python
Create an unprivileged container for NVIDIA GPUs in LXC
Learning notes for the migrations feature in the Django framework (3)
Learning notes for the migrations feature in the Django framework (1)
Recursively search for files and directories in Python and output