How to select 20 records by condition?

Question

SQLite

I need to select with an id, say 300 20 records that pass by the condition: if at least one tag (eng, anime, history) is included in the tags record field.
Here's what I've come up with so far: SELECT * FROM memes LIMIT 20 OFFSET {last_id - 21}
I don't know how to write the condition. I need to return 20 records that pass by a condition.

Answer 1

Just substrings, but then you will find not tags, but substrings:

SELECT * FROM memes WHERE tags LIKE '%eng%' AND tags LIKE '%history%' AND tags LIKE '%anime%' ORDER BY id DESC LIMIT 20

If the tags are separated by spaces ( " tag1 tag2 tag3 " ), it will search for whole words:

SELECT * FROM memes WHERE tags LIKE '% eng %' AND tags LIKE '% history %' AND tags LIKE '% anime %' ORDER BY id DESC LIMIT 20

If there are spaces inside the tags themselves, then you need to first replace them in the tag with some character. If the spaces in the tags themselves are replaced by "_" with an underscore, and not something else, then this will complicate the query a bit. Because " _ " and " % " are reserved characters in the LIKE operator, so if these characters occur in tags, you will need to add Escaping, for example for the " rus_eng " and " 100% " tags:

SELECT * FROM memes WHERE tags LIKE '% rus\_eng %'  ESCAPE "\" AND tags LIKE '% 100\% %' ESCAPE "\" ORDER BY id DESC LIMIT 20

(and do not forget that the backslash character itself causes problems in the code, so it might be better to replace it with something else, for example, with " / ")
If the tags are separated by commas (" ,tag1,tag2,tag3, "), you can look for substrings like " ,tag1, " :

SELECT * FROM memes WHERE tags LIKE '%,eng,%' AND tags LIKE '%,history,%' AND tags LIKE '%,anime,%' ORDER BY id DESC LIMIT 20

or like this:

SELECT * FROM memes WHERE instr(tags, ',eng,') AND instr(tags, ',history,') AND instr(tags, ',anime,') ORDER BY id DESC LIMIT 20

or, if separated by spaces ( " tag1 tag2 tag3 " ):

SELECT * FROM memes WHERE instr(tags, ' eng ') AND instr(tags, ' history ') AND instr(tags, ' anime ') ORDER BY id DESC LIMIT 20

That's all for SQLite
If you are for Microsoft or Oracle, then read about CONTAINS - it works faster.
Yes, and if you need an intersection on any of the tags - just use the OR operator instead of AND in the above code.

Answer 2

SELECT * FROM memes WHERE id > 300 AND tags in ('eng', 'anime', 'history') LIMIT 20