How to do full text search with JOIN in Rails?

Question

ruby

There is a table of documents. And it has several links to organization tables and contact persons.
There is a document search field, after entering a request in which a search should be carried out in all fields. It seems that you can just make a full text index and search for it. But the problem is that you also need to search by the name of the organization and the full name of the contact person, and only the IDs of these entities are stored in the document table.
What solution can be used here? As I understand it, Sphinx can do it all, and do it quickly. But the issue of performance is not worth it here and I would like a simpler solution, without the use of external dependencies.

Answer 1

Check out the screencast from codeschool. They decided to do a search like at Basecamp
. They made a blank and went to show it to DHH (David Heinemeier Hansson) himself, the founder of rails and Basecamp. He reviewed their code.
There is a rather primitive code, literally 10-20 lines) But it's interesting.

Answer 2

If it is not possible to use an external search engine, this can be solved using separate tables. You make separate tables in which you store only information for full-text search. Write triggers for inserting, updating, deleting records in the tables that you are going to do a full-text search - collect information in the trigger that is not available by direct query on the table.
There is also an option to support these tables not through triggers, but through ActiveRecord hooks.
Well, we must not forget that in MySQL full-text search is only for MyISAM tables.
PS Still, I recommend that you use Sphinx or ElasticSearch - save yourself a big headache.