Possible to select result set as a comma delimited list?

[Darren884]

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?

Thanks so much,
Darren

[wwb_99]

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.

[Darren884]

Yes but the array the data is from is multidimensional.

[Darren884]

Never mind, I have created a multidimensional in_array function.

[r937]

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

what you're searching for is a join query, most likely

dead simple sql, if the tables are designed properly

constructing comma-delimited strings is not efficient in sql, so perhaps were you thinking of doing consecutive queries?

a join would still be better

[Darren884]

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;

[r937]

could you please indicate which table the module, controller, and method columns are in?

[Darren884]

Hi Rudy, here is the final actual query:

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.

[r937]

sorry, your explanation made no sense to me whatsoever, but the fault is entirely mine, i assure you

the reason i asked is because i think some of those conditions actually belong in the ON clause rather than the WHERE clause