Simon,
Think I figured out the issue, but no idea how to solve for it. In the DB
I was using the Groups has a foreign key I didn't think was importnant and
didn't include in the trimmed down example. So I was very confused when
writing the file that the issue didn't repeat until I compared the SQL.
Apparently the join is using the foreign key from Groups which connects to
Users.id even when not stated. Is there any way to fix this? I've
repproduced the problem with the new schema and code below which should be
runnable and illustrate what's going on.
Here is the file (also attached as a .py) after the SQL:
2018-06-01 11:08:10,111 INFO sqlalchemy.engine.base.Engine SELECT
group_members.id AS group_members_id, group_members.group_id AS
group_members_group_id, group_members.user_id AS group_members_user_id,
`groups`.id AS groups_id, `groups`.group_number AS groups_group_number,
`groups`.created_by AS groups_created_by, users.id AS users_id,
users.username AS users_username
FROM group_members INNER JOIN `groups` ON `groups`.group_number =
group_members.group_id INNER JOIN users ON users.id = `groups`.created_by
WHERE group_members.group_id = %(group_id_1)s
2018-06-01 11:08:10,111 INFO sqlalchemy.engine.base.Engine {'group_id_1':
'2-5'}
from flask import Flask, render_template
from flask_sqlalchemy import SQLAlchemy
app = Flask(__name__)
app.config['SQLALCHEMY_DATABASE_URI'] =
'mysql+pymysql://root:pass@localhost/sand2'
app.config['SQLALCHEMY_TRACK_MODIFICATIONS'] = False
app.config['SQLALCHEMY_ECHO'] = True
db = SQLAlchemy(app)
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)
created_by = db.Column(db.Integer, db.ForeignKey('users.id'),
nullable=False)
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)
if __name__ == '__main__':
db.session.create_all()
first_user = Users(username="Bill")
second_user = Users(username="Jen")
third_user = Users(username="Bobby")
db.session.add(first_user)
db.session.add(second_user)
db.session.add(third_user)
db.session.commit()
first_group = Groups(group_number="1-3", created_by=third_user.id)
second_group = Groups(group_number="2-5", created_by=third_user.id)
db.session.add(first_group)
db.session.add(second_group)
db.session.commit()
first_relation = GroupMembers(group_id=first_group.group_number,
user_id=first_user.id)
second_relation = GroupMembers(group_id=first_group.group_number,
user_id=third_user.id)
third_relation = GroupMembers(group_id=second_group.group_number,
user_id=second_user.id)
fourth_relation = GroupMembers(group_id=second_group.group_number,
user_id=third_user.id)
db.session.add(first_relation)
db.session.add(second_relation)
db.session.add(third_relation)
db.session.add(fourth_relation)
db.session.commit()
q = db.session.query(GroupMembers, Groups, Users)
q = q.join(Groups).join(Users)
q = q.filter(GroupMembers.group_id == '2-5').all()'''
On Friday, June 1, 2018 at 9:07:45 AM UTC-5, chbr...@gmail.com wrote:
>
> 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> 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.
>> > To post to this group, send email to sqlal...@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.
from flask import Flask, render_template
from flask_sqlalchemy import SQLAlchemy
app = Flask(__name__)
app.config['SQLALCHEMY_DATABASE_URI'] = 'mysql+pymysql://root:pass@localhost/sand2'
app.config['SQLALCHEMY_TRACK_MODIFICATIONS'] = False
app.config['SQLALCHEMY_ECHO'] = True
db = SQLAlchemy(app)
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)
created_by = db.Column(db.Integer, db.ForeignKey('users.id'), nullable=False)
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)
if __name__ == '__main__':
db.session.create_all()
first_user = Users(username="Bill")
second_user = Users(username="Jen")
third_user = Users(username="Bobby")
db.session.add(first_user)
db.session.add(second_user)
db.session.add(third_user)
db.session.commit()
first_group = Groups(group_number="1-3", created_by=third_user.id)
second_group = Groups(group_number="2-5", created_by=third_user.id)
db.session.add(first_group)
db.session.add(second_group)
db.session.commit()
first_relation = GroupMembers(group_id=first_group.group_number, user_id=first_user.id)
second_relation = GroupMembers(group_id=first_group.group_number, user_id=third_user.id)
third_relation = GroupMembers(group_id=second_group.group_number, user_id=second_user.id)
fourth_relation = GroupMembers(group_id=second_group.group_number, user_id=third_user.id)
db.session.add(first_relation)
db.session.add(second_relation)
db.session.add(third_relation)
db.session.add(fourth_relation)
db.session.commit()
q = db.session.query(GroupMembers, Groups, Users)
q = q.join(Groups).join(Users)
q = q.filter(GroupMembers.group_id == '2-5').all()'''
