Join multiple tables in SQLAlchemy/Flask

Question

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.

Answer 1

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