How to Do A Database Count Through Records Containing Comma Separated Values?

Server Side Components General
#1

I have a table of contacts, which have a field called contact_tags. Each tag recorded is the id from the table lookups.

To keep things (possibly) easy, for each contact I’m just storing the contact_tags as a comma separated string.

So some lookups records may be:

id     lookup_name
==================
100    Rock
101    Country
102    Classical
103    Funk

and some contacts records may look like:

id   name   contact_tags (string)
=================================
1    Fred    100,101
2    John    102,100,103
3    Jane    103

I want to count the number of contacts who have the tag of Rock.

Do you know a neat way I can do that within a Server Action?

1 Like
#2

A query like the following would do:

SELECT COUNT(*) AS total FROM contacts WHERE contact_tags LIKE '%100%'

It will only work when your ids start at 100, if you have an id 10 then it would not match correctly.

You can generate the above query with the query builder, use the count aggregate function and the contains filter.

#3

Thanks for your reply @patrick!

I could well have ids of 123 and also 1234 so am I right in saying that when searching for 123 it would also incorrectly give me the 1234 entry?

I was thinking I need something which is more precise and works along the lines of the .split() function in Wappler…

Maybe I should just not be so lazy, and also have a contact_contact_tags table too…

#4

When you have such a table, then that would be a lot easier and more efficient.

Alternative when you use MySQL you could maybe use FIND_IN_SET

SELECT COUNT(*) FROM contacts WHERE FIND_IN_SET("100", contact_tags) > 0
1 Like