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.
