在SQLAlchemy/Flask中联接多个表

本文介绍了在SQLAlchemy/Flask中联接多个表的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述

我试图找出SQLAlchemy中正确的联接查询设置，但似乎无法解决.

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'))

所以关系如下:

• 1:n小组成员
• 1:n会员商品
• 1:n版本项

我想通过从数据库中选择具有特定版本的所有项目行来构建查询.然后，我想按组然后按成员排序.使用Flask/WTForm的输出应如下所示:

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.

推荐答案

以下将在一个查询中为您提供所需的对象:

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)

但是，您得到的结果将是元组(Group, Member, Item, Version)的列表.现在由您决定以树形形式显示它.不过，以下代码可能会很有用:

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::如果您愿意放弃前两个关系的lazy = 'dynamic'，则可以查询以使用代码加载整个object network(与上述元组相对) :

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