redmine all users projects query

Ever needed to get all projects associated with every user in redmine?  Here’s a query that will help

Multiple Joins sql query; To get user, all projects associated with user and role on that project.

 Exmaple:

Table

Column Names

users

id, firstname, lastname, login

roles

role_id, name

member_roles

member _id, role_ id

members

user_id, project_ id

select  users.firstname, users.lastname, users.login, projects.name,  roles.name from members

inner JOIN member_roles
ON member_roles.member_id=members.id
inner Join users
ON users.id=user_id
inner Join projects
ON projects.id=project_id
inner Join roles
ON roles.id=member_roles.role_id
order by user_id ASC

redmine all users projects query

Multiple Joins sql query; To get user, all projects associated with user and role on that project.

Leave a Reply

  

  

  

You can use these HTML tags

<a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>