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 <chbrn...@gmail.com> 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+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. -- 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.