Join multiple tables in SQLAlchemy/Flask

flask, join, python, sqlalchemy

Solution

Following will give you the objects you need in one query:

q = (session.query(Group, Member, Item, Version)
        .join(Member)
        .join(Item)
        .join(Version)
        .filter(Version.name == my_version)
        .order_by(Group.number)
        .order_by(Member.number)
        ).all()
print_tree(q)

However, the result you get will be a list of tuples `(Group, Member, Item, Version)`. Now it is up to you to display it in a tree form. Code below might prove useful though:

def print_tree(rows):
    def get_level_diff(row1, row2):
        """ Returns tuple: (from, to) of different item positions.  """
        if row1 is None: # first row handling
            return (0, len(row2))
        assert len(row1) == len(row2)
        for col in range(len(row1)):
            if row1[col] != row2[col]:
                return (col, len(row2))
        assert False, "should not have duplicates"

    prev_row = None
    for row in rows:
        level = get_level_diff(prev_row, row)
        for l in range(*level):
            print 2 * l * " ", row[l]
            prev_row = row

Update-1: If you are willing to forgo `lazy = 'dynamic'` for the first two relationships, you can a query to load a whole `object network` (as opposed to tuples above) with the code:

q = (session.query(Group)
        .join(Member)
        .join(Item)
        .join(Version)
        # @note: here we are tricking sqlalchemy to think that we loaded all these relationships,
        # even though we filter them out by version. Please use this only to get data and display,
        # but not to continue working with it as if it were a regular UnitOfWork
        .options(
            contains_eager(Group.member).
            contains_eager(Member.items).
            contains_eager(Item.version)
            )
        .filter(Version.name == my_version)
        .order_by(Group.number)
        .order_by(Member.number)
        ).all()

# print tree: easy navigation of relationships
for g in q:
    print "", g
    for m in g.member:
        print 2 * " ", m
        for i in m.items:
            print 4 * " ", i

Problem

I am trying to figure out the correct join query setup within SQLAlchemy, but I can't seem to get my head around it. I have the following table setup (simplified, I left out the non-essential fields): ``` class Group(db.Model): id = db.Column(db.Integer, primary_key = True) number = db.Column(db.SmallInteger, index = True, unique = True) member = db.relationship('Member', backref = 'groups', lazy = 'dynamic') class Member(db.Model): id = db.Column(db.Integer, primary_key = True) number = db.Column(db.SmallInteger, index = True) groupid = db.Column(db.Integer, db.ForeignKey('group.id')) item = db.relationship('Item', backref = 'members', lazy = 'dynamic') class Version(db.Model): id = db.Column(db.Integer, primary_key = True) name = db.Column(db.String(80), index = True) items = db.relationship('Item', backref='versions', lazy='dynamic') class Item(db.Model): id = db.Column(db.Integer, primary_key = True) member = db.Column(db.Integer, db.ForeignKey('member.id')) version = db.Column(db.Integer, db.ForeignKey('version.id')) ``` So the relationships are the following: - 1:n Group Member - 1:n Member Item - 1:n Version Item I would like to construct a query by selecting all Item-Rows from the database, that have a certain version. Then I would like to order them by Group and then by Member. The output using Flask/WTForm should look something like this: ``` * GroupA * MemberA * ItemA (version = selected by user) * ItemB ( dito ) * Member B * ItemC ( dito ) .... ``` I have come up with something like the following query, but I am pretty sure that it is not correct (and inefficient) ``` session.query(Item,Member,Group,Version) .join(Member).filter(version.id==1) .order_by(Group).order_by(Member).all() ``` My first intuitive approach would have been to create something like ``` Item.query.join(Member, Item.member==Member.id) .filter(Member.versions.name=='MySelection') .order_by(Member.number).order_by(Group.number) ``` but obviously, this doesn't work at all. The join operation on the Version table does not seem to produce the type of join between the two tables that I expected. Maybe I am totally misunderstanding the concept, but after reading the tutorials this would have made sense to me.

Original source