carambo carambo - 3 months ago 7
Python Question

Query instances related to other instance with SQLAlchemy

I have

User
and
Property
models for my application. I was originally using the Peewee ORM, but am switching to SQLAlchemy. I want to select all properties listed by the current user and display them in a template. In Peewee, iterating over the following query worked, but I get a
ProgrammingError
in SQLAlchemy. How do I get the instances related to another instance in SQLAlchemy?

my_properties = Property.query.filter_by(listed_by=current_user)




{% for property in my_properties %}
{{ property.listing_no }}




class Property(db.Model):
id = db.Column(db.Integer, primary_key=True)
listed_by = db.Column(db.Integer, db.ForeignKey('users.id'))

Answer

In Peewee, a foreign key field does not separate the actual key from the thing it represents. In SQLAlchemy, a foreign key column is only the key, a relationship is used to represent the thing it points to.

listed_by is an integer. current_user is a proxy to a User object. SQLAlchemy doesn't know what integer == User means.

Create a relationship to the User object and filter on that.

class Property(db.Model):
    id = db.Column(db.Integer, primary_key=True
    listed_by_id = db.Column(db.ForeignKey(User.id))
    listed_by = db.relationship(User, foreign_keys=[listed_by_id])
Property.query.filter_by(listed_by=current_user)

Technically, you can also filter on the id field by passing the user's id, although it's usually more useful to define the relationship.

Property.query.filter_by(listed_by_id=current_user.id)
Comments