Full text searches

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?



94.1k

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:

http://docs.phalconphp.com/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! :)

edited Aug '14

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.