Django MySQL full text search

Question

I need to implement full text search for my Django application, running MySQL as backend.

Let's say I've got a model as follows:

class MyItem(models.Model):
    title = models.CharField()
    short_description = models.TextField()
    description = models.TextField()

I would like to have results first for search term occurences in title, then in short_description and at the end in description field. I'll be happier if I don't have to use additional modules/applications for this task.

Kubis

Answer 1

The previously highest rated answer is deprecated. As of Django 1.10 there is no more search field lookup for MySQL databases (see the search section in the 1.10 documentation).

The release notes for 1.10 also propose a solution to this, by defining a custom lookup:

###__search query lookup

The search lookup, which supports MySQL only and is extremely limited in features, is deprecated. Replace it with a custom lookup:

from django.db import models

class Search(models.Lookup):
   lookup_name = 'search'

   def as_mysql(self, compiler, connection):
       lhs, lhs_params = self.process_lhs(compiler, connection)
       rhs, rhs_params = self.process_rhs(compiler, connection)
       params = lhs_params + rhs_params
       return 'MATCH (%s) AGAINST (%s IN BOOLEAN MODE)' % (lhs, rhs), params

models.CharField.register_lookup(Search)
models.TextField.register_lookup(Search)

Lymphoid

This needs to be higher now, much more up-to-date answer + no dependency to external packages. – Norval

This worked well me too on Django 2+, thanks for sharing it! – Stinko

This is working for me however it fails during tests - thought I would drop this here in case someone using this snippet has the same issue: #61487225 – Stinko

Answer 2

You can use full text search in django

MyItem.objects.filter(title__search="some search text")

One thing is - you can't define a fulltext index from a Django model, you need to do in directly in a database (using PHPMyAdmin or SQL query)

See Django documentation for field lookup called search

Counteraccusation

This doesn't work by default when using MySQL because it doesn't use the table type MyISAM. You get the error "The used table type doesn't support FULLTEXT indexes" – Roehm

@SilverLight how to do it against multiple columns like for compound FULLTEXT search index? – Zinn

@RohitKhatri you can build complex lookups with Q objects in Django: docs.djangoproject.com/en/2.2/topics/db/queries/…. The main feature here would be that it allows to connect the query sets with "or". – Lymphoid

@Silver Light Maybe I am missing it, but I can't seem to find the referenced field lookup called search?! Was that removed maybe? – Lymphoid

This looks like Postgres only feature docs.djangoproject.com/en/4.1/ref/contrib/postgres/search/… – Diggins

Answer 3

I don't know if it helps now but I've created a new Python Library for Django which supports MySQLs' and MariaDBs' native full-text search engine over one or multiple columns:

You can have a look at it on the GitHub repository

There's also a description how to install it, use it and how to create the FULLTEXT INDEX stuff via Django migrations (Django 1.7+).

If you've configured the indexes and set the SearchManager for your model you should be able to run something like:

Mymodel.objects.search('Something')
Mymodel.objects.search('Somet*')
Mymodel.objects.search('+Something -Awesome')

Just wanted to update this topic because I didn't find an acceptable solution so far and it might help someone out there as well :)

Cheers Domi

Jeramyjerba

Answer 4

If you are looking for a beefy solution I recommend http://haystacksearch.org/

It is very well thought out.

Evocator

I suppose I will end up with 3rd party solution which I tried to avoid. – Kubis

Yeah you might need to clarify why that is the solution you want? this option will keep you database independent and it scales well as you add db models to the search criteria. For instance on a project I started using haystack for one table but the final product was querying ten. Don't forget though If you know exactly what you want in raw sql terms you can use it. docs.djangoproject.com/en/dev/topics/db/sql . Good luck – Evocator

Answer 5

Django provides full-text functionality for PostgreSQL's only.

Spokane

Answer 6

From django docs regarding full-text search:

Example:

Entry.objects.filter(headline__search="+Django -jazz Python")

SQL equivalent:

SELECT ... WHERE MATCH(tablename, headline) AGAINST (+Django -jazz Python IN BOOLEAN MODE);

Note this is only available in MySQL and requires direct manipulation of the database to add the full-text index. By default Django uses BOOLEAN MODE for full text searches. See the MySQL documentation for additional details.

Now to the direct manipulation of the database. In MySQL you can create full-text index by following these steps (source article):

• Open command prompt, and enter mysql -u root -p. On prompt enter the root password.
• Enter use your_db_name to switch to your django database.
• Enter CREATE FULLTEXT INDEX index_name ON table_name (column_names).

That's it! FTS indexing in enabled in your django database. Now you can use the django's rich QuerySet API to do full-text searches.

Edit: The above quote no longer exists in the django version >1.9.

Wilow

I cannot find your quote anywhere in the links provided. – Coggins

@Coggins That's because that part no longer exists in the django version greater than 1.9. I have updated the url so that it takes you to the version which is relevant to my answer at the time writing. – Lector

See answer by tbr for a workaround for Django > 1.9 where __search was removed from mysql – Stinko