'Django way to query multi column fulltext index in MySQL?
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!
Sources
This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.
Source: Stack Overflow
| Solution | Source |
|---|
