Project

General

Profile

Forums » Help »

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

    (1-2/2)