SQL Database table structure
Added by Jorgen Skogmo almost 14 years ago
Hi, Im writing an external app that will pull its data from the redmine database.
I would like to get a list of Users and their Roles for all Projects
and I cant find out wich table stores the project-user-role relationship...
This is what I have now:
SELECT
users.firstname, users.lastname
FROM users
INNER JOIN members
ON users.id=members.user_id
WHERE members.project_id=1
Any help on this would be awesome!
Thanks in advance,
j.
Replies (2)
RE: SQL Database table structure
-
Added by Felix Schäfer almost 14 years ago
I have this to get all mails of user with a given role in a given project, I think you'll be able to figure out the rest by yourself:
SELECT `users`.`mail` FROM `members`, `users`, `projects`, `member_roles`, `roles`
WHERE `users`.`status`=1 # Only active users
AND `users`.`type` = "USER" # Only users, not groups
AND `members`.`project_id` = `projects`.`id`
AND `projects`.`identifier` = "[% param.0 %]"
AND `members`.`user_id` = `users`.`id`
AND `members`.`id` = `member_roles`.`member_id`
AND `roles`.`id` = `member_roles`.`role_id`
AND `roles`.`name` = "[% param.1 %]";
Furthermore, the first- and lastname combination is not guaranteed to be unique, you should rather use the login field for that.
RE: SQL Database table structure
-
Added by Jorgen Skogmo almost 14 years ago
Excellent!
Here is my result, thanks a lot!
SELECT
users.mail as email, users.firstname, users.lastname, roles.name as role
FROM users, members, projects, member_roles, roles
WHERE projects.id = 149
AND members.user_id = users.id
AND members.project_id = projects.id
AND members.id = member_roles.member_id
AND roles.id = member_roles.role_id
AND users.status=1