I'm trying to use the query builder to build a full text search.
This is my code:
$builder = self::getQueryBuilder()
->from('table')
->where('MATCH(column) AGAINST (:query: IN BOOLEAN MODE)');
$result = $builder->getQuery()
->execute(array('query' => $query));But all I get is this error:
Syntax error, unexpected token IDENTIFIER(AGAINST), near to ' (:query: IN BOOLEAN MODE)'How can I perform a full text search?
MATCH/AGAINST is a MySQL extension that cannot be translated to the other database systems supported like PostgreSQL/Sqlite.
You can use Raw SQL to execute specific database extensions like this:
https://docs.phalcon.io/en/latest/reference/phql.html#using-raw-sql
The information on the above link is new or a was I blind?
Thank you for your answer! :)
Have you looked at Sphinxsearch project? It works way better than mysql or postgre.
Fulltext search is a feature that will definitely slow things down in your site the more data is entered in your tables. In addition to this your results and performance from the FULLTEXT are a function of the minimum length of words you will allow to be indexed.
As @roman-kulish mentioned Sphinx is definitely a good and extremely fast alternative. Solr is also another alternative.
For a project that will scale easily, you can use the interface of one of those (Solr, Sphinx) and create code that will create the indexed data/documents for your records, and then maintain it by deleting/editing/adding to the document index when needed. Your search then will become a matter of querying Solr/Sphinx vs. using MySQL. The Solr/Sphinx server could very well be in a different box and scale as your project grows.
Yes, I'm aware of solutions like Sphinx and Solr. I'm using them on several projects and I intend to use Solr on this particular project where I'm also using phalcon.
As for the mysql fulltext search, I'm only using it using it on a specific query on the website's backoffice while I don't have Solr up and running! :)
Anyway... thank you for your advices! :)
How about https://mariadb.com/kb/en/fulltext-index-overview?
performance is very slow... i tested a lot of FTS for our projects... Our one of projects uses Apache SOLR with cutom GIS extension... other projects use ElasticSearch... and i think ElasticSearch is best for current time.
