8

I'm trying to retrieve data from multiple tables with SQLAlchemy using the .join() method.

When I run the query I was expecting to get a single object back which had all the data from the different tables joined so that I could use a.area_name and so on where area_name is on one of the joined tables. Below is the query I am running and the table layout, if anyone could offer insight into how to achieve the behavior I'm aiming for I would greatly appreciate it! I've been able to use the .join() method with this same syntax to match results and return them, I figured it would return the extra data from the rows as well since it joins the tables (perhaps I'm misunderstanding how the method works or how to retrieve the information via the query object?).

If it helps with the troubleshooting I'm using MySQL as the database

query:

a = User.query.filter(User.user_id==1).join(UserGroup,
 User.usergroup==UserGroup.group_id).join(Areas, User.area==Areas.area_id).first()

and the tables:

class User(db.Model):
    user_id = db.Column(db.Integer, primary_key=True)
    name = db.Column(db.String(20), unique=True)
    usergroup = db.Column(db.Integer, db.ForeignKey('user_group.group_id'), nullable=False)
    area = db.Column(db.Integer, db.ForeignKey('areas.area_id'), nullable=False)

class UserGroups(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    group_id = db.Column(db.Integer, nullable=False, unique=True)
    group_name = db.Column(db.String(64), nullable=False, unique=True)


class Areas(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    area_id = db.Column(db.Integer, nullable=False, unique=True)
    area_name = db.Column(db.String(64), nullable=False, unique=True)
Carl
  • 151
  • 1
  • 2
  • 10

2 Answers2

6

So it seems that I need to use a different approach to the query, and that it returns a tuple of objects which I then need to parse.

What worked is:

 a = db.session.query(User, UserGroups, Areas
 ).filter(User.user_id==1
 ).join(UserGroup,User.usergroup==UserGroup.group_id
 ).join(Areas, User.area==Areas.area_id
 ).first()

The rest remaining the same. This then returned a tuple that I could parse where the data from User is a[0], from UserGroups is a[1], and Areas is a[2]. I can then access the group_name column with a[1].group_name etc.

Hopefully this helps someone else who's trying to work with this!

danblack
  • 12,130
  • 2
  • 22
  • 41
Carl
  • 151
  • 1
  • 2
  • 10
5

Take a look at SQLAlchemy's relationship function:

http://docs.sqlalchemy.org/en/latest/orm/basic_relationships.html#one-to-many

You may want to add a new attribute to your User class like so:

group = sqlalchemy.relationship('UserGroups', back_populates='users')

This will automagically resolve the one-to-many relationship between User and UserGroups (assuming that a User can only be member of one UserGroup at a time). You can then simply access the attributes of the UserGroup once you have queried a User (or set of Users) from your database:

a = User.query.filter(...).first()
print(a.group.group_name)

SQLAlchemy resolves the joins for you, you do not need to explicitly join the foreign tables when querying.

The reverse access is also possible; if you just query for a UserGroup, you can access the corresponding members directly (via the back_populates-keyword argument):

g = UserGroup.query.filter(...).first()
for u in g.users:
    print(u.name)
jbndlr
  • 4,965
  • 2
  • 21
  • 31
  • 2
    I'll give it a try, still don't completely understand the way the ORM handles these, but that's something I need to fix anyway to be able to use SQLAlchemy properly. At the moment I'm more familiar with joins so trying to stick with that. Your code example above and the link give me a good place to start to learn and explore the ORM's attributes and relationships (which I'm guessing makes all this easier!) – Carl May 14 '18 at 09:02