StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

POFulltext search on many tables
text
Body
copied!<p>I have three tables, all of which have a column with a fulltext index. The user will enter search terms into a single text box, and then all three tables will be searched.</p> <p>This is better explained with an example:</p> <pre><code>documents doc_id name FULLTEXT table2 id doc_id a_field FULLTEXT table3 id doc_id another_field FULLTEXT </code></pre> <p>(I realise this looks stupid but that's because I've removed all the other fields and tables to simplify it).</p> <p>So basically I want to do a fulltext search on <code>name</code>, <code>a_field</code> and <code>another_field</code>, and then show the results as a list of <code>documents</code>, preferably with what caused that document to be found, e.g. if <code>another_field</code> matched, I would display what <code>another_field</code> is.</p> <p>I began working on a system whereby three fulltext search queries are performed and the results inserted into a table with a structure like:</p> <pre><code>search_results table_name row_id score </code></pre> <p>(This could later be made to cache results for a few days with e.g. a hash of the search terms).</p> <p>This idea has two problems. The first is that the same document can be in the search results up to three times with different scores. Instead of that, if the search term is matched in two tables, it should have one result, but a higher score.</p> <p>The second is that parsing the results is difficult. I want to display a list of documents, but I don't immediately know the <code>doc_id</code> without a join of some kind; however the table to join to is dependant on the <code>table_name</code> column, and I'm not sure how to accomplish that.</p> <p>Wanting to search multiple related tables like this must be a common thing, so I guess what I'm asking is am I approaching this in the right way? Can someone tell me the best way of doing it please.</p>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload