2

I have User(db.Model) and Photo(db.Model) and one to many relation between them, thus user has many photos.

class User(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    photos = db.relationship('Photo', backref='user', lazy='dynamic')
    def to_json(self):
        json = {
            'id': self.id,
            'photos': [photo.to_json() for photo in self.photos] if self.photos else None,
            'name': self.name
        }
        return json
class Photo(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    user_id = db.Column(db.Integer, db.ForeignKey('user.id'))
    name = db.Column(db.String(1024), nullable=False)

    def to_json(self):
        json = {
            'id': self.id,
            'user_id': self.user_id,
            'name': self.name
        }
        return json

My goal was to select all of the users with all their photos with single query, and I did it using native query:

from sqlalchemy import text
statement = text('SELECT u.*, p.* FROM user u LEFT JOIN photo p ON u.id=p.user_id')
user_photo_query = db.session.query(User, Photo).from_statement(statement)
user_photo_tuples = user_photo_query.all()
users_dict = {}
db.session.expunge_all()
db.session.close()
for u, p in user_photo_tuples:
    if not users_dict.get(u.id, None):
        u.photos = list()
        users_dict.update({u.id: u})
    users_dict.get(u.id).photos.append(p)
users = users_dict.values()
return jsonify({'users': [user.to_json() for user in users]})

In the result of this query there is list of tuples (User_240298428, Photo_20394823). The photo is different in every record of this list. The user is the same for some records of the list. Because I don't want the users and the photos object to be bound to the session anymore I do:

db.session.expunge_all()
db.session.close()

So I iterate through the list and try to add all photos in the corresponding user.photos list.

users_dict.get(u.id).photos.append(p)

Here I get error:

Parent instance is not bound to a Session; lazy load operation of attribute 'user' cannot proceed

This is because of the relation that the user object has to the photo object:

photos = db.relationship('Photo', backref='user', lazy='dynamic')

What I want is to use user.photos as regular list, and not as SQLAlchemy relation. I want to add photos to the user list without this being connected somehow to sqlalchemy. Hope it's clear

4
  • Please provide a minimal reproducible example. Include proper models, the statement you're using, and the serialization code you've hinted at in the comments of the answer, so that possible future readers may easily understand what's this about (serializing to JSON with related objects). Commented Nov 2, 2017 at 10:08
  • @Ilja Everilä Yes is like XY problem, but still I would be glad to have easy way to do this, that I asked in the header of the question. What you proposed in the comments below (in the answer) is kind of solution because I will remove the lazy loading, and don't want really to do that because I need it for the rest of the code. (Will add more code though) Commented Nov 2, 2017 at 10:12
  • 1
    A solution for keeping your current dynamic relationship prop as is, albeit not-so-beautiful, would be to add another relationship property used for joined loading, with a default of lazy='raise'. So if you by accident try to access it in code, you get an exception and it does not incur joins on every query, unless you've included .options(joinedload('photos_collection')) or such to your query. Commented Nov 2, 2017 at 10:24
  • @Ilja Everilä I have edited with more code. Hope now looks OK. Thank you for the note. Commented Nov 2, 2017 at 10:30

1 Answer 1

2

Something makes me think that you don't make use of your sqlalchemy models properly. Why, instead of detaching from session and doing weird stuff with your relation (many tuples with same user?), can't you leverage creating list of photos per user by using mechanism that is intended to that by SQLalchemy?

If you have your relation between User and Photo, you can either:

  • change relationship loading strategy to default (delete lazy attribute at all or set it to 'select'), and then query database for Users:

    users = db.session.query(User).all()
    

    ...and access user's photos as a list of db objects.

    user.photos # photos is a list of all user's photos
    
  • with lazy='dynamic' you can reach all users the same way, but now photos is also a query (read more on: dynamic relationships):

    A key feature to enable management of a large collection is the so-called “dynamic” relationship. This is an optional form of relationship() which returns a Query object in place of a collection when accessed

    ...and like with every query, you can use its API (filter(), first(), or in your case all() for getting all of the photos.

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

3 Comments

Because user.photos is query. That's why. I want to serialize the user list to json. - > 'photos' : [photo.to_json() for photo in self.photos] if self.photos else None . So this would make one more query to the database and I don't want to. I want it fast, very fast. Also If I don't user lazy, these queries would be fired when I retrieve the users with users = db.session.query(User).all(). So queries are slower then iterating I think. I'm not sure about all of this of course. In Java this was the fastest solution that worked for problem like this. Now I tried to solve it same way.Might be erro
For every user the photos serialization would make one more query to the database. This means len(users) more queries , no matter if I use eager or lazy.
"Also If I don't user lazy, these queries would be fired when I retrieve the users with users = db.session.query(User).all()" is a bit of a misunderstanding. The default loading behaviour issues a SELECT when the relationship attribute is accessed the first time. What you might be interested in is joined eager loading, where a single query does pretty much what you're trying to do manually and populates the relationship collection.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.