#!/usr/local/bin/python

from sqlalchemy import *

db = create_engine('mysql://x:x@localhost/x')
conn = db.connect()

metadata = BoundMetaData(db)
metadata.engine_echo = True

users = Table('users', metadata, autoload=True)
user_groups = Table('user_groups', metadata, autoload=True)
user_group_members = Table('user_group_members', metadata, autoload=True)
roles = Table('roles', metadata, autoload=True)
role_groups = Table('role_groups', metadata, autoload=True)
role_group_members = Table('role_group_members', metadata, autoload=True)
group_access = Table('group_access', metadata, autoload=True)

#
# What I want to achieve:
#
# SELECT
#     rgm.role_id AS role,
#     MIN(ga.access_perm) AS has_access
# FROM
#     role_group_members rgm
# INNER JOIN role_groups rg           ON rg.id = rgm.role_group_id
# INNER JOIN group_access ga          ON ga.role_group_id = rg.id
# INNER JOIN user_groups ug           ON ug.id = ga.user_group_id
# INNER JOIN user_group_members ugm   ON ugm.user_group_id = ug.id
# WHERE
#     ugm.user_id = '3' AND
#     rgm.role_id = '9'
# GROUP BY
#     rgm.role_id
#

columns_c = [ role_group_members.c.role_id, func.min(group_access.c.access_perm) ]

where_c = and_ ( user_group_members.c.user_id == '3',
                 role_group_members.c.role_id == '9' )

join_c = role_group_members \
            .join ( role_groups,         role_groups.c.id == role_group_members.c.role_group_id  ) \
            .join ( group_access,        group_access.c.role_group_id == role_groups.c.id        ) \
            .join ( user_groups,         user_groups.c.id == group_access.c.user_group_id        ) \
            .join ( user_group_members,  user_group_members.c.user_group_id == user_groups.c.id  )

group_by_c = [ role_group_members.c.role_id ]

q = select(columns_c, where_c, from_obj=[join_c], group_by=[group_by_c])

print str(q)

#
# And the result ...
# 
# SELECT role_group_members.role_id, min(group_access.access_perm) 
# FROM role_group_members 
# JOIN role_groups ON role_groups.id = role_group_members.role_group_id 
# JOIN group_access ON group_access.role_group_id = role_groups.id 
# JOIN user_groups ON user_groups.id = group_access.user_group_id 
# JOIN user_group_members ON user_group_members.user_group_id = user_groups.id 
# WHERE user_group_members.user_id = %s AND role_group_members.role_id = %s 
# GROUP BY [Column('role_id',<sqlalchemy.databases.mysql.MSInteger object at 0x84c8a4c>,key='role_id',primary_key=False,nullable=False,default=Passive