Hey all, I am trying to optimize my code, I am currently using MySQL but I posted this in here because I would prefer something that would work with multiple SQL databases (e.g. PostGreSQL, MSSQL). What I am trying to do is return a result set that is comma delimited. For instances, I am selecting all the role_ids of a particular user. I would like to store those role_ids in a variabe as a comma separated list so I can use it in a WHERE IN statement instead of having to do a loop and use WHERE role_id =. Is this possible while working with the above databases and MySQL or if not, is there a MySQL only solution?
Could you do this? Yes. Could you poke yourself in the eye with a sharp stick? Yes.
Just about any language I can think of has some sort of “find in array” function which is at least as, if not more, efficient than “find in string” for small datasets. Nevermind that arrays are much, much more flexible than some csv.
Hi Rudy, after thinking a while I wen d’oh. Thanks so much here is something like I did:
SELECT *
FROM gp_users_roles
LEFT JOIN gp_permissions
ON gp_permissions.role_id = gp_users_roles.role_id
WHERE (module = '' OR module IS NULL)
AND (controller = '' OR controller IS NULL)
AND(method = '' OR method IS NULL)
AND gp_users_roles.role_id = 1;
SELECT COUNT(*)
FROM `gp_users_roles`
JOIN `gp_permissions` ON `gp_permissions`.`role_id` = `gp_users_roles`.`role_id`
WHERE (module = '' OR module IS NULL)
AND (controller = 'users' OR controller IS NULL)
AND (method = 'dashboard' OR method IS NULL)
AND `gp_users_roles`.`user_id` = 2
I didn’t specify where module, controller, and method are coming from because I am using an active record class with a database prefix and it will defeat the database prefix cause because of the way I have to write those WHERE with OR statements in them. I can assure you though they are not ambiguous.
