Redmine

turns out not to be too complicated, the SQL would look something like:

 1SELECT *
 2FROM   "issues" 
 3LEFT OUTER JOIN "issue_statuses" 
 4  ON "issue_statuses".id = "issues".status_id
 5LEFT OUTER JOIN "projects" 
 6  ON "projects".id = "issues".project_id
 7WHERE 
 8       ( 1 = 0
 9           OR projects.is_public = 't'
10           OR projects.id IN ( 5, 6, 7 )
11        )

where 5,6,7 are the projects the user has the 'view_issues' permission.

And here is my Python code which uses SQLAlchemy queries:

 1def apply_user_filter_to_query(user_id, q):
 2    rmuser = db.sess.query(RmUser).get(user_id)
 3    if not rmuser:
 4        raise ValueError('user_id %s not found' % user_id)
 5
 6    # if the user is an administrative user, no filter is applied
 7    if not rmuser.admin:
 8        proj_ids = user_projects_with_view_issue_perms(user_id)
 9        return q.where(or_(
10            text( '1 = 0'), 
11            projects.c.is_public == True,
12            projects.c.id.in_(proj_ids)
13        ))
14    return q
15
16def user_projects_with_view_issue_perms(user_id):
17    sql = """ 
18        select p.id
19        from projects p
20        inner join members m
21        on m.project_id = p.id
22        inner join member_roles mr
23        on mr.member_id = m.id
24        inner join roles r
25        on r.id = mr.role_id
26        where m.user_id = :user_id and r.permissions like '%\:view_issues%'
27    """ 
28    result = db.sess.execute(sql, {'user_id': user_id})
29    return [row['id'] for row in result]
30
31def issues_list_users(user_id, fusers, fstatuses, is_grouped):
32    issue_select = apply_user_filter_to_query(user_id, iss_select_all)
33    ...
34user_issues = issues_list_users(session_user.id, ...)