CODEWITHSUNDEEP
pythonsqlalchemy
jblz
jblz

Reputation: 1029

Flask / SQLAlchemy - unable to query relationship properly

I have the following code in Flask. I want users to be able to follow specified RSS feeds, which is saved in the 'relationships_feed' table but I'm unable to get this outputting correctly.

Models

class Relationships_Feed(db.Model):
    __tablename__ = 'relationships_feed'
    feed_id = db.Column(db.Integer, db.ForeignKey('feeds.id'), primary_key=True)
    user_id = db.Column(db.Integer, db.ForeignKey('users.id'), primary_key=True)


class Feed(db.Model):
    __tablename__ = 'feeds'
    id = db.Column(db.Integer, primary_key=True)
    feed_url = db.Column(db.String(255), index=False, unique=True)
    feed_name = db.Column(db.String(64), index=True)

class User(UserMixin, db.Model):
    __tablename__ = 'users'
    id = db.Column(db.Integer, primary_key=True)
    confirmed = db.Column(db.Boolean, default=False)
    first_name = db.Column(db.String(64), index=True)
    last_name = db.Column(db.String(64), index=True)
    email = db.Column(db.String(64), unique=True, index=True)
    password_hash = db.Column(db.String(128))
    role_id = db.Column(db.Integer, db.ForeignKey('roles.id'))
    feeds = db.relationship('Feed', secondary='relationships_feed', backref='user', lazy='dynamic')

Here is how I'm calling the code whilst troubleshooting the issue.

The expected outcome is that user.feeds returns all feeds he is subscribed to, based on the data in the relationships_feed table.

Calling code

users = db.session.query(User)

for user in users:
    print (user.first_name)
    print (user.last_name)
    print (user.email)
    print (user.feeds)

Output (fake data in db)

11:21:40 web.1    | Robert
11:21:40 web.1    | Williams
11:21:40 web.1    | [email protected]
11:21:40 web.1    | SELECT feeds.id AS feeds_id, feeds.feed_url AS feeds_feed_url, feeds.feed_name AS feeds_feed_name
11:21:40 web.1    | FROM feeds, relationships_feed
11:21:40 web.1    | WHERE ? = relationships_feed.user_id AND feeds.id = relationships_feed.feed_id

All I receive back is the raw query itself, which makes me think I'm not querying it correctly but I've been through multiple guides and documentation and still unable to resolve. Is this a simple fix I'm missing?

Upvotes: 1

Views: 1022

Answers (1)

Zohaib Ijaz
Zohaib Ijaz

Reputation: 22935

You need to call .all() on a lazy relationship

feeds = db.relationship('Feed', secondary='relationships_feed', backref='user', lazy='dynamic')

In your model, you made this relationship lazy, which means that on accessing feeds property, it will not get all items but you have to explicitly get all those items either by calling user.feeds.all() or use it like iterator.

for feed in user.feeds:
    print(feed)

You can get all feeds at once

feeds = user.feeds.all()

In you scenario, 

for user in users:
    print (user.first_name)
    print (user.last_name)
    print (user.email)
    print (user.feeds.all()

Upvotes: 3

Related Questions