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

To create code blocks or other preformatted text, indent by four spaces:

    This will be displayed in a monospaced font. The first four 
    spaces will be stripped off, but all other whitespace
    will be preserved.
    
    Markdown is turned off in code blocks:
     [This is not a link](http://example.com)

To create not a block, but an inline code span, use backticks:

Here is some inline `code`.

For more help see http://daringfireball.net/projects/markdown/syntax

You can use these HTML tags

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