Flask-SQLAlchemy: How to return a list of single objects after performing a join operation?

Question

I have two tables Users and Organization.

I have the following SQL mapped classes:

class User(db.Model):
    __tablename__ = 'user'
    id = db.Column('id', db.Integer, primary_key=True)
    first_name = db.Column('first_name', db.String)
    second_name = db.Column('second_name', db.String)
    last_name = db.Column('last_name', db.String)
    # organization_id = db.Column('organization_id', db.Integer)
    organization_id = db.Column('organization_id',db.Integer,db.ForeignKey('organization.id'),nullable=False)
    # organization = db.relationship('Organization',backref='user',lazy=False)
    email = db.Column('email', db.String)
    mobile_no = db.Column('mobile_no', db.String)
    designation = db.Column('designation', db.String)
    role_id = db.Column('role_id', db.Integer)
    password = db.Column('password', db.String)

class Organization(db.Model):
    __tablename__ = 'organization'
    id = db.Column('id',db.Integer,primary_key=True)
    name = db.Column('name',db.String)
    type_id = db.Column('type_id',db.Integer)
    ward_id = db.Column('ward_id',db.Integer)
    attrs = ['id','name','type_id','ward_id']
    user_organization = db.relationship('User',backref='organization',lazy=False)

I want to perform a join operation on User.onrganization_id and Organization.id with the results looking like this

user.id | user.first_name organization.name | user.email | user.mobile_no

I tried this code:

q = db.session.query(User,Organization).join(Organization).all()

This give me a result as list with collection of object:

[(<User 2>, <Organization 1>)]

I want the return type to be a list of single object and not a collection of objects. Like this

[(<UsersAndOrganization1>)] //Name of the object doesn't have to be the same

Mikhail Burshteyn · Accepted Answer · 2018-09-27 06:28:13Z

2

Since you have lazy=False on your relationship, SQLAlchemy will perform the join automatically when you do User.query.all(). In the query results each user will have organization property populated, so you will be able to access its fields as, for example, user.organization.name.

Regardless of the lazy option in the relationship configuration, you can always achieve the same effect by explicitly specifying load options in the query:

users = User.query.options(db.joinedload(Organization)).all()

More info can be found in the docs here (lazy=False is synonym to lazy='joined').

Also, try enabling SQLALCHEMY_ECHO in your Flask app config and see the actual SQL queries emitted by SQLAlchemy. It is always very useful to see what happens under the hood.

answered Sep 27, 2018 at 6:28

Mikhail Burshteyn

5,0421 gold badge19 silver badges29 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

D14TEN Over a year ago

That worked, thanks but I am trying to access the organization name through a single object and not object within a object. I need to access the organization name through some like this "user.name". Is it possible?

Mikhail Burshteyn Over a year ago

Why do you need them in one object? With your configuration the whole organization row is fetched from database anyway.

Mikhail Burshteyn Over a year ago

An option would be to add a @property on the User class returning self.organization.name

pyjamas Over a year ago

For anyone still looking for an answer to get one object I put an answer here stackoverflow.com/a/60883545/3620725

Collectives™ on Stack Overflow

Flask-SQLAlchemy: How to return a list of single objects after performing a join operation?

1 Answer 1

4 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related