gistfile1.py

# Attempt to turn http://paste2.org/p/2878916 into SQLAlchemy code
# I've had to make some guesses about the intent, though.
from sqlalchemy import *
metadata = MetaData()

group_table = Table('group_table', metadata, Column('group_id', Integer, primary_key=True), Column('type', String), Column('description', String))

contact_table = Table('contact_table', metadata, Column('client_id', Integer, primary_key=True), Column('group_id_1', Integer, ForeignKey(group_table.c.group_id)), Column('group_id_2', Integer, ForeignKey(group_table.c.group_id)), Column('group_id_3', Integer, ForeignKey(group_table.c.group_id)))


group_id_constraint = or_(group_table.c.group_id == contact_table.c.group_id_1, group_table.c.group_id == contact_table.c.group_id_2, group_table.c.group_id == contact_table.c.group_id_3)

myjoin = group_table.join(contact_table, group_id_constraint)

query = select([group_table.c.group_id, group_table.c.type, group_table.c.description, select([func.count(contact_table.c.client_id)], from_obj=myjoin).as_scalar().label('member_count')], from_obj=myjoin).group_by(group_table.c.group_id).offset(0).limit(30)

"""
Produces this SQL:

SELECT group_table.group_id, group_table.type, group_table.description, (SELECT count(contact_table.client_id) AS count_1 
FROM group_table JOIN contact_table ON group_table.group_id = contact_table.group_id_1 OR group_table.group_id = contact_table.group_id_2 OR group_table.group_id = contact_table.group_id_3) AS member_count 
FROM group_table JOIN contact_table ON group_table.group_id = contact_table.group_id_1 OR group_table.group_id = contact_table.group_id_2 OR group_table.group_id = contact_table.group_id_3 GROUP BY group_table.group_id
 LIMIT :param_1 OFFSET :param_2
 
Where of course param_1 and param_2 are 30 and 0 respectively.
"""