Simon, Will put a standalone script together, I'm using Flask-SQLAlchemy so it might vary some from standard SQLAlchemy, I'll post it in a few minutes, thanks! Carl
On Friday, June 1, 2018 at 8:51:32 AM UTC-5, Simon King wrote: > > What does the SQL look like for each of your queries? > > Can you produce a standalone script that demonstrates the problem? You > could use my script from > https://groups.google.com/forum/#!topic/sqlalchemy/GNIBQMvMRg8 as a > template. > > Thanks, > > Simon > > On Fri, Jun 1, 2018 at 2:41 PM <chbr...@gmail.com <javascript:>> wrote: > > > > I'm trying to join three tables in SQLAlchemy and while it works on > joining and filtering with two of the tables the third one it only returns > an arbitrary row (always the same one for some reason) and I'm completely > confused about this behavior. > > > > Table classes: > > > > class Users(db.Model): > > id = db.Column(db.Integer, primary_key=True) > > username = db.Column(db.String(64), index=True, unique=True, > nullable=False) > > > > class Groups(db.Model): > > id = db.Column(db.Integer, unique=True) > > group_number = db.Column(db.String(30),index=True, unique=True, > nullable=False, primary_key=True) > > > > class GroupMembers(db.Model): > > id = db.Column(db.Integer, primary_key=True) > > group_id = db.Column(db.String(64), > db.ForeignKey('groups.group_number'), nullable=False) > > user_id = db.Column(db.Integer, db.ForeignKey('users.id'), > nullable=False) > > > > I'm trying to retrieve the results where a certain group_id is filtered > by, and all the rows that match that in the relationship table > (GroupMembers) and all the Users rows that correspond to the matching IDs. > > > > I've tried three things: > > > > q = db.session.query(GroupMemers, Groups, Users) > > q = q.join(Groups).join(Users) > > q = q.filter(GroupMembers.group_id == 5).all() > > > > This returns the expected tuples, except that it only returns the same > row from Users over and over with each tuple: > > > > for row in q: > > print(row) > > (<GroupMembers 1>, <Groups 5>, <Users 3>) > > (<GroupMembers 2>, <Groups 5>, <Users 3>) > > (<GroupMembers 3>, <Groups 5>, <Users 3>) > > (<GroupMembers 4>, <Groups 5>, <Users 3>) > > (<GroupMembers 5>, <Groups 5>, <Users 3>) > > > > which is correct, in that there are 5 rows which should match in > GroupMembers, but each one of those rows lists a different User.id in the > foreign key GroupMembers.user_id, and none of them match id 3. > > > > The second thing I tried was switching the orders of the tables, so > Users was the first table in the list: > > > > q = db.session.query(Users, Groups, GroupMembers) > > q = q.join(Groups).join(GroupMemebrs) > > q = q.filter(GroupMembers.group_id == 5).all() > > > > but the results were exactly the same, just in the order indicated in > the query tuple. > > > > The third thing I tried doing was using a filter() to match > GroupMembers.user_id to Users.id, but this returned 0 matching rows, even > though there should be 5. > > > > q = db.session.query(GroupMemers, Groups, Users) > > q = q.join(Groups).join(Users) > > q = q.filter(Users.id == FacilityStaff.user_id) > > q = q.filter(GroupMembers.group_id == 5).all() > > > > I'm a bit perplexed and confused at this behavior. Without using > backrefs or anything like that can anyone help me with what I might be > doing wrong here on the joins and filtering? > > > > Thanks! > > > > -- > > SQLAlchemy - > > The Python SQL Toolkit and Object Relational Mapper > > > > http://www.sqlalchemy.org/ > > > > To post example code, please provide an MCVE: Minimal, Complete, and > Verifiable Example. See http://stackoverflow.com/help/mcve for a full > description. > > --- > > You received this message because you are subscribed to the Google > Groups "sqlalchemy" group. > > To unsubscribe from this group and stop receiving emails from it, send > an email to sqlalchemy+...@googlegroups.com <javascript:>. > > To post to this group, send email to sqlal...@googlegroups.com > <javascript:>. > > Visit this group at https://groups.google.com/group/sqlalchemy. > > For more options, visit https://groups.google.com/d/optout. > -- SQLAlchemy - The Python SQL Toolkit and Object Relational Mapper http://www.sqlalchemy.org/ To post example code, please provide an MCVE: Minimal, Complete, and Verifiable Example. See http://stackoverflow.com/help/mcve for a full description. --- You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+unsubscr...@googlegroups.com. To post to this group, send email to sqlalchemy@googlegroups.com. Visit this group at https://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/d/optout.
