Join Multiple Tables In Sqlalchemy/flask

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 no

Solution 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:

defprint_tree(rows):
    defget_level_diff(row1, row2):
        """ Returns tuple: (from, to) of different item positions.  """if row1 isNone: # first row handlingreturn (0, len(row2))
        assertlen(row1) == len(row2)
        for col inrange(len(row1)):
            if row1[col] != row2[col]:
                return (col, len(row2))
        assertFalse, "should not have duplicates"

    prev_row = Nonefor row in rows:
        level = get_level_diff(prev_row, row)
        for l inrange(*level):
            print2 * 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 relationshipsfor g in q:
    print"", g
    for m in g.member:
        print 2 * " ", m
        for i in m.items:
            print 4 * " ", i