Select in SQLite filtering a String column with separator like array

Question

I have to add a column on my users table to filter by personalized tags. The idea is to store them in my sqlite database separated by ',' or ';' or some kind of separator.

How could I make a select filtering by those values?

The values of that column can change, so I need a method that enables adding or editing those tags in other queries.

Something like

SELECT * 
FROM users
WHERE tags contain especific tag or tags

or are there any better practice for doing this? (that don't require a new index table)

Don't do it like that. Have a new table instead, that stores the tags per user row by row. — sticky bit, Nov 21 '19 at 13:42

score 3 · Accepted Answer · answered Nov 21 '19 at 13:43

The idea is to store them in my sqlite database separated by ',' or ';' or some kind of separator

It looks like it's not too late yet not to hurt yourself... Before you go further, consider reading this advice.

The correct way to represent that 1-N relationship in your relational database is to create a separated table, eg user_tags, whose structure should look like:

user_tags
    id       -- unique identifier (optional)
    user_id  -- reference to users(id)
    tag      -- name of the tag

Possibly, you could enhance this model by creating a table to store the name of the tags (an other meta information if needed), and reference the id of the table in the user_tags table, whose structure would look like:

tags
    id       -- unique identifier
    name     -- tag name

user_tags
    id       -- unique identifier (optional)
    user_id  -- reference to users(id)
    tag_id   -- reference to tags(id)

I need a method that enables adding or editing those tags in other queries.

Using a comma-separated list, this will be painful. With a separate table, you can use simple joins for this, or an exists condition to check for multiple tags:

-- get all users who own at least one of these tags
select u.*
from users u
where exists (
    select 1
    from user_tags ut
    inner join tags t on ut.tag_id = t.id and t.name in ('foo', 'bar', 'baz')
    where ut.user_id = u.id
)

Ok thanks, I think the same, about using a new table, but my team lader insists on this method. I think that I can convince him whith those arguments. — CVO, Nov 21 '19 at 14:19
Welcome @Noark! You will find many more arguments in the excellent answer by Bill Karwin that I linked in my answer. — GMB, Nov 21 '19 at 14:24
and really good ones. I voted his answer too. Lots of thanks again — CVO, Nov 21 '19 at 14:29

score 1 · Answer 2 · answered Nov 21 '19 at 13:44

The standard/conventional approach to the problem you describe is to have a separate table mapping user ids to tags. There is much to be said for such an approach, but if you want to keep the tags in the users table, I’d suggest considering the JSON1 extension.

In particular, given SQLite’s assortment of functions supporting JSON, consider using a JSON object to hold all the tags. Each tag would be a key. This way, you can easily add and remove tags, and even create indices based on the tags.

Thanks as I commented to he first answer I think the same, about using a new table, but my team lader insists on this method. I think that I can convince him whith those arguments. — CVO, Nov 21 '19 at 14:22

Select in SQLite filtering a String column with separator like array

2 Answers2