Menu
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:
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.
967
I've found that the following works to join two tables: result = session. query(User, Document). select_from(join(User, Document)). filter(User.
Python Flask and SQLAlchemy ORM Effect of joining is achieved by just placing two tables in either the columns clause or the where clause of the select() construct. Now we use the join() and outerjoin() methods. The join() method returns a join object from one table object to another.
Step 1 - Install the Flask-SQLAlchemy extension. Step 2 - You need to import the SQLAlchemy class from this module. Step 3 - Now create a Flask application object and set the URI for the database to use. Step 4 - then use the application object as a parameter to create an object of class SQLAlchemy.
The error message is telling you that SQLAlchemy can't determine how to join the two tables users and friendships, because there is more than one foreign key linking them. You need to explicitly define the join condition.
The session.query will join table_1 to table_2 on ID and ID_1 columns and uses the filter condition as provided. And lastly, it will print the expected rows of the table_1 after all conditions get applied. The JOIN and the FILTER condition can be changed as per the use case.
Now that we have two tables, we will see how to create queries on both tables at the same time. To construct a simple implicit join between Customer and Invoice, we can use Query.filter () to equate their related columns together. Below, we load the Customer and Invoice entities at once using this method −
The answer is simple and it’s related to how LEFT JOIN works. It takes the first table ( customer ) and joins all its rows (4 of them) to the next table ( city ). The result of this is 4 rows because the customer could belong to only 1 city.
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 If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
