Search a serialized array (or a better method?)

Yii 2.0 General Discussions
1

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

USERS
=====================================
user_id | user_name | member_of_group
=====================================

GROUPS
=====================
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

2

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
2 Likes
3

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.

4

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

5

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

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