I’m using Teampass, here is a minimised example of the tables schema
Teampass_Roles_Title :
+----+----------------------+ | id | title | +----+----------------------+ | 1 | Title 1 | | 2 | Title 2 | | 3 | Title 3 | | 4 | Title 4 | | 5 | Title 5 | | 6 | Title 6 | ... +----+----------------------+
and Teampass_Users
+------------+------------------------------------------------------------+ | login | fonction_id | +------------+------------------------------------------------------------+ | AAA | | | BBB | | | CCC | 12;21; | | DDD | 3;4;5;6;7;8;9;14;15; | | EEE | 3;7; | | FFF | 3;7; | | GGG | 3;4;5;6;7;8;9;14;15;21; | | HHH | 3;4;5;6;7;8;9;14;15;21; | ... +------------+------------------------------------------------------------+
the numbers in fonction_id are the ids from the table Teampass_Roles_Title
Instead of using an intermediate table, they opted for this model, and I dont know if there is any way to have for example a liste of all users that access to Title 6
+------------+------------------------------------------------------------+ | login | fonction_id | +------------+------------------------------------------------------------+ | DDD | 3;4;5;6;7;8;9;14;15; | | GGG | 3;4;5;6;7;8;9;14;15;21; | | HHH | 3;4;5;6;7;8;9;14;15;21; | ... +------------+------------------------------------------------------------+
Advertisement
Answer
This is a really bad data model. Sometimes, we are stuck with other people’s really, really bad decisions. You can do what you want using find_in_set() and replace():
where find_in_set(6, replace(function_id, ';', ',')) > 0