CODEWITHSUNDEEP
sqlitefull-text-searchsql-like
Majid Hojati
Majid Hojati

Reputation: 1790

SQLITE Perform Reverse full text search

Imagine we have two tables, One named tags and one named texts as follows:

tags table:

id name
1 T1
2 T2
3 T3
4 T4

Texts table:

id text
1 Text includes T2
2 Text includes T4
3 Text
4 Text includes T2

I want to search each Text and attach the tags from Tags table to each text. The output will be something like this:

tagId textId
2 1
2 4
4 3

I is like a loop over each text and then loop over each tag and check if that text includes that tag or not using Like statement. However this is not practical. The other method would be using FTS but I am not quite sure how to write its query since for example this query gives me only texts which have those tags

select *
  from texts_fts
 WHERE texts_fts MATCH ( select group_concat(tag, ' OR ')
                              from keywords
                             group by 'x' )

Is there any other approaches for getting this to work? Thanks

Upvotes: 1

Views: 160

Answers (1)

forpas
forpas

Reputation: 164099

You can do it with a join and the operator LIKE in the ON clause:

SELECT tg.id tagId, tx.id textId
FROM tags tg INNER JOIN texts tx
ON tx.text LIKE '%' || tg.name || '%'

Or with INSTR():

SELECT tg.id tagId, tx.id textId
FROM tags tg INNER JOIN texts tx
ON INSTR(tx.text, tg.name)

See the demo.

Upvotes: 1

Related Questions