Search a serialized array (or a better method?)

In my app i have a Users and a Groups table. users can be part of one or more groups.

user_id | user_name | member_of_group

group_id | group_name

At the moment I saved an imploded array using § as a separator. I think it should be better to use serialize and unserialize instead.
Now i have to write a function that retrieves all User IDs of a certain group and i’m STUCK!
Any suggestion? I thought to make a first reduction querying with something similar to php’s substr() and then explode each and every “member_of_group”.
This because I suppose that if I search for “2” as substr, db will give me “false positives” for, eg., 12, 20, 21, 22 and so on…

my head is about to explode

Hi Massimo, I have two suggestions to you:

  1. Create a join table (users_groups) with the keys from both tables. This usually is the best approach and there’s tons of tutorials on how to do it.
  2. Save member_of_group as JSON, instead of a custom type field. Yii2 have MySQL and Postgres JSON support since version 2.0.14. Check it out here: JSON in MySQL and PostgreSQL

Way 1 that @bpanatta suggested should be way better performance-wise and later you’ll be able to perform auto-complete on group names when selecting ones for a user.

Thank you both for now. Be prepared: I’m afraid I’ll bother you again…

You know. Everything is too obvious to be asked only when you fully grasp something. And this one was so obvious now…

  1. PostgreSQL has support for arrays + GIN index for search. JSON is quite a wasteful format, though.
1 Like