Join by verm

New About Yours API Help
2.9 KB, Python 2
#What I want to is join four tables to count how many distinct "LoggerRole" names exist for each "Organization"
#
#Essentially I want to end up with something like:
#
#{"org_1": {'logger_role_1': 2, 'logger_role_2': 5, 'logger_role_3: 7 },
# "org_2": {'logger_role_1': 9, 'logger_role_2': 3, 'logger_role_3: 4 },
# "org_3": {'logger_role_1': 1, 'logger_role_2': 8, 'logger_role_3: 2 }} 
#
#... and so forth.
#
# My best attempt so far has been to get the count of logger_roles per organization, but what I really want is the count of each distinct logger_role per organization.
#
# My stripped down tables look like this:

from app import db

class User(db.Model):
    id = db.Column(db.Integer, db.Sequence('user_id_seq'), autoincrement=True, primary_key=True)
    name = db.Column(db.String())
    organization_id = db.Column(db.Integer, db.ForeignKey('organization.id'))
    organization = db.relationship('Organization', backref=db.backref('users', lazy='dynamic'))

    def __init__(self, name, organization):
        self.name = name
        self.organization = organization

    def __repr__(self):
        return '<User id={id} name="{name}" organization_id={organization_id}>'.format(
            id=self.id,
            name=self.name,
            organization_id=self.organization_id
        )


class Organization(db.Model):
    id = db.Column(db.Integer, db.Sequence('organization_id_seq'), autoincrement=True, primary_key=True)
    name = db.Column(db.String())

    def __init__(self, name):
        self.name = name

    def __repr__(self):
        return '<Organization id={id} name="{name}">'.format(
            id=self.id,
            name=self.name
        )


class Logger(db.Model):
    id = db.Column(db.Integer, db.Sequence('logger_id_seq'), autoincrement=True, primary_key=True)
    user_id = db.Column(db.Integer, db.ForeignKey('user.id'))
    user = db.relationship('User', backref=db.backref('loggers', lazy='dynamic'))
    logger_role_id = db.Column(db.Integer, db.ForeignKey('logger_role.id'))
    logger_role = db.relationship('LoggerRole', backref=db.backref('loggers', lazy='dynamic'))

    def __init__(self, user, logger_role):
        self.user = user
        self.logger_role = logger_role

    def __repr__(self):
        return '<Logger id={id} user_id={user_id} logger_role_id={logger_role_id}>'.format(
            id=self.id,
            user_id=self.user_id,
            logger_role_id=self.logger_role_id
        )

class LoggerRole(db.Model):
    id = db.Column(db.Integer, db.Sequence('logger_role_id_seq'), autoincrement=True, primary_key=True)
    name = db.Column(db.String())

    def __init__(self, name):
        self.name = name

    def __repr__(self):
        return '<LoggerRole id={id} name="{name}">'.format(
            id=self.id,
            name=self.name
        )


# Any help would be greatly appreciated... <3
Pasted 4 days, 23 hours ago — Expires in 3 days
URL: http://dpaste.com/31N0X1M