Flask Many To Many Join As Done By Prefetch_related From Django

I have following Group and Contact model in flask with Sql Alchemy ORM group_contact = db.Table( 'group_contact', db.Column('group_id', db.Integer, db.ForeignKey( '

Solution 1:

You want to tell SQLAlchemy to eagerly load related objects by using a relationship loading technique. SQLAlchemy can be told to load the groups together with the contacts in a single query.

For just this one query, you can add joinedload() option (it is available via the Flask-SQLAlchemy db object):

contacts = Contact.query.options(db.joinedload(Contact.groups)).all()

This pre-loads the Contact.groups attribute on each matched contact:

for contact in contacts:
    # no new query issued to fetch groups, the data for the groups# is already availableprint(contact.groups)

The query executed looks like this:

SELECT 
    contact.id AS contact_id,
    contact.phone AS contact_phone,
    group_1.id AS group_1_id,
    group_1.name AS group_1_name
FROM contact 
LEFTOUTERJOIN (
    group_contact AS group_contact_1
    JOIN "group" AS group_1 ON group_1.id = group_contact_1.group_id
) ON contact.id = group_contact_1.contact_id

You can also set a default loading strategy for the relationship on the model; to always eagerly load groups, use lazy='joined' on the relationship:

class Contact(db.Model):
    # ...groups = db.relationship(
        "Group", secondary=group_contact, backref='contacts',
        lazy='joined')