CODEWITHSUNDEEP
phpmysqldistancelevenshtein-distance
Nilpo
Nilpo

Reputation: 4816

MySQL search with word distance across neighboring records

I am performing a keyword text search across static records in a MySQL database. Is it possible to construct a query that finds the first keyword in a record and the second keyword in a neighboring record? Consider the following sample data.

------------------------------------------------------
| id | textstrings                                   |
------------------------------------------------------
|  1 | Every good boy does fine.                     |
|  2 | The quick brown fox jumped over the lazy dog. |
|  3 | I will not eat green eggs and ham.            |
|  4 | There is no time like the present.            |
|  5 | Envy is an ugly shade of green.               |
------------------------------------------------------

A search for the terms green brown should return records 2 and 3 since they are neighboring records, but should not contain record 5 since it is not a neighbor with record 3.

I know that I can perform a query for either of the words and accomplish this by processing the result set, but I would like to know if it's possible to build this into the query.

This field does have a FULLTEXT index.

Upvotes: 4

Views: 275

Answers (1)

Tim Biegeleisen
Tim Biegeleisen

Reputation: 520968

You can join together two queries, one which will search for ids containing the first search string (green in this example), and the other which will search for ids containing the second search string (brown here).

The SELECT statement is structured the way it is to remove duplicate adjacent id pairs which may occur as a result of the WHERE clause.

SELECT LEAST(t1.id, t2.id) AS id1, GREATEST(t1.id, t2.id) AS id2
FROM
(
    SELECT id
    FROM table
    WHERE textstrings LIKE '%green%'
) t1
INNER JOIN
(
    SELECT id
    FROM table
    WHERE textstrings LIKE '%brown%'
) t2
ON t1.id = t2.id - 1 OR t1.id = t2.id + 1
GROUP BY LEAST(t1.id, t2.id), GREATEST(t1.id, t2.id)

Click the link below for a running demo.

SQLFiddle

Upvotes: 3

Related Questions