Django way to query multi column fulltext index in MySQL?

Question

I'm trying to query a multi-column fulltext index in Django using a Lookup. I have the following lookup

@CharField.register_lookup
@TextField.register_lookup
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

This is inspired from the lookup that I found here in the Django docs.

However, this appears to only work for Fulltext indexes of a single column. I'm trying to do a query over two columns. Below is my MySQL table for reference:

CREATE TABLE Entry (
    entryID     INTEGER     UNSIGNED NOT NULL AUTO_INCREMENT,
    username    VARCHAR(20) NOT NULL,
    time        DATETIME    NOT NULL,
    modified    BOOLEAN     NOT NULL,
    message     TEXT        NOT NULL,
    FULLTEXT (message, username),
    PRIMARY KEY (entryID)
);

Of course when I try to do a query using the lookup above, I get an error:

"django.db.utils.OperationalError: (1191, "Can't find FULLTEXT index matching the column list"

I noticed there is Django code to accomplish this using a SearchVector, but only for PostgresSQL.

Additionally, I realize there is a package that someone wrote to do this at https://blog.confirm.ch/django-1-8-mysql-mariadb-full-text-search/

However, I really wanted to know if there is a way I can accomplish easily without additional packages. Thank you!

Django way to query multi column fulltext index in MySQL?

0 Answers0