0

I have a case where we are maintaining a table containing resources. This table has a varchar column that contains role ids as comma separated values (I know normalizing SHOULD have been the way to go, but can't change a long running working system). E.g. role_ids column contains '1,4,6,9,10' and another row contains '5,10,15'.

Then, for a user in system, I have the associated role ids as a list, e.g. 4,15. Now I need to find 'any in many', i.e. any resource that may have any of the role ids present in resource.role_ids column.

This question is something similar to this one, but the solution expected is not expected in Grails.

I'm looking for a MySQL solution - either a query or a stored procedure. Though finding a set of resources could have been achieved using 'FIND_IN_SET()', but don't want to perform multiple calls to DB with each of user's role_id list.

Community
  • 1
  • 1
dotrc
  • 171
  • 2
  • 5
  • 3
    The SHOULD isn't strong enough and the 'can't change a long running working system' probably has less effect than you think. This is such a crappy issue and design that you will spend far longer in the long run hacking around it, fixing problems, and trying to do things like this, than you would spend dedicating to removing that bad design from the code in the first place. It's a working system yes, but you're not gonna just throw in a better solution presumably, you should plan, implement and test it. Very very very strong recommendation from me. [Plus, it looks like just a few tables] – Kieren Johnstone Jul 11 '11 at 14:55

1 Answers1

1

Use a function like this one, to turn your lists into individual records, then join everything up normally.

Community
  • 1
  • 1
MatBailie
  • 83,401
  • 18
  • 103
  • 137