Doctrine2 - Use Full Text and MyIsam

Question

I am building an application in Symfony2 using Doctrine2 with mysql. I would like to use full-text search. I can’t find much about how to implement this - right now I am fixated on how to configure the table engine on myisam.

It seems that it is not possible to set the table type using annotations. Also, if I did this manually by running the ALTER TABLE query, I'm not sure if Doctrine2 will work properly - does it depend on InnoDB foreign keys?

Is there a better place to ask these questions?

Answer 1

Introduction

Doctrine2 uses InnoDB, which supports foreign keys used in Doctrine associations. But since MyISAM does not yet support this, you cannot use MyISAM to manage Doctrine objects.

On the other hand, MySQL v5.6, which is currently under development, will receive InnoDB FTS support and thus enable full-text search in InnoDB tables.

SOLUTIONS

So there are two solutions:

• Use MySQL v5.6 at your own risk and crack a little doctrine to implement the MATCH AGAINST method: link in French (I could translate if necessary, but still have errors, and I would not recommend this solution)

• As described by quickshifti, create a MyISAM table with a full-text index to perform the search. Because Doctrine2 resolves native SQL queries and how you can map this query to an entity ( here) .

EXAMPLE FOR THE 2nd DECISION

Consider the following tables:

table 'user' : InnoDB [id, name, email] table 'search_user : MyISAM [user_id, name -> FULLTEXT] 

Then you just need to write a search query using JOIN and collation (in the repository):

 <?php public function searchUser($string) { // 1. Mapping $rsm = new ResultSetMapping(); $rsm->addEntityResult('Acme\DefaultBundle\Entity\User', 'u'); $rsm->addFieldResult('u', 'id', 'id'); $rsm->addFieldResult('u', 'name', 'name'); $rsm->addFieldResult('u', 'email', 'email'); // 2. Native SQL $sql = 'SELECT u.id, u.name FROM search_user AS s JOIN user AS u ON s.user_id = u.id WHERE MATCH(s.name) AGAINST($string IN BOOLEAN MODE)> 0; // 3. Run the query $query = $this->_em->createNativeQuery($sql, $rsm); // 4. Get the results as Entities ! $results = $query->getResult(); return $results; } ?> 

But the FULLTEXT index must remain relevant. Instead of using the cron task, you can add triggers (INSERT, UPDATE, and DELETE) as follows:

 CREATE TRIGGER trigger_insert_search_user AFTER INSERT ON user FOR EACH ROW INSERT INTO search_user SET user_id=NEW.id, name=NEW.name; CREATE TRIGGER trigger_update_search_user AFTER UPDATE ON user FOR EACH ROW UPDATE search_user SET name=name WHERE user_id=OLD.id; CREATE TRIGGER trigger_delete_search_user AFTER DELETE ON user FOR EACH ROW DELETE FROM search_user WHERE user_id=OLD.id; 

So that your search_user table always gets the latest changes.

Of course, this is just an example, I wanted to keep it simple, and I know that this request can be made using LIKE.

Answer 2

The doctrine removed the full-text search feature from v1 when moving to Doctrine2. You may have to roll back your own full-text search support in Doctrine2.

I am considering using migrations to generate the tables themselves, by running search queries using the built-in SQL query query to get sets of identifiers that relate to tables managed by Doctrine, then using the indicated sets of identifiers to hydrate records, usually through a doctrine.

Cron is probably something periodic for updating full-text tables.