0

The user on my website can have multiple roles. I would like to display products to the user depending on the role he has.

My products table has the field "roles" with string as data type and it can have one or more roles, separated by comma. Example for a product:

product_roles: 'role2, role5, role6'

My variable $rolesLoggedInUser contains the following array:

$rolesLoggedInUser = ['role3', 'role5', 'role7']

In my repository, I have the following query:

$qb->select('p')
   ->where('p.roles IN :rolesLoggedInUser')
   ->setParameter('rolesLoggedInUser', $rolesLoggedInUser)

My goal is that my query returns all data rows in which the field product_roles contains one or more roles contained in $rolesLoggedInUser. How do I achieve that?

Max
  • 832
  • 1
  • 14
  • 33
  • By concatenating the product roles into a single string you have made your life very difficult. Refactor your db so that a table product_roles has product_id(int with foreign key) & role(string). After that you can use a sub query. – Ne Ma Jun 10 '16 at 10:53
  • Yep. What @NeilMasters said. It's possible to do something like: http://stackoverflow.com/questions/1865353/combining-like-and-in-for-sql-server but a product role table will be much more performant and less hassle. And make sure you put parens around :rolesLoggedInUser – Cerad Jun 10 '16 at 10:55

0 Answers0