CODEWITHSUNDEEP
pythonpython-2.7sqlalchemyflask-sqlalchemy
LAdas
LAdas

Reputation: 509

SQLAlchemy: @property mapping?

I have following models:

class Details(db.Model):

    details_id = db.Column(db.Integer, primary_key=True)
    details_main = db.Column(db.String(50))
    details_desc = db.Column(db.String(50))

class Data(db.Model):

    data_id = db.Column(db.Integer, primary_key=True)
    data_date = db.Column(db.Date)
    details_main = db.Column(db.String(50))

    @property
    def details_desc(self):

        result = object_session(self).\
            scalar(
                select([Details.details_desc]).
                    where(Details.details_main == self.details_main)
            )

        return result

Now, I would like to run query using filter which depends on defined property. I get empty results (of course proper data is in DB). It doesn't work because, probably, I have to map this property. The question is how to do this? (One limitation: FK are not allowed).

Data.query\
    .filter(Data.details_desc == unicode('test'))\
    .all()

Upvotes: 2

Views: 2290

Answers (1)

Dag Høidahl
Dag Høidahl

Reputation: 8345

You can implement this with a regular relationship and an association proxy:

class Data(db.Model):
    data_id = db.Column(db.Integer, primary_key=True)
    data_date = db.Column(db.Date)
    details_main = db.Column(db.String(50))

    details = relationship(
        Details,
        primaryjoin=remote(Details.details_main) == foreign(details_main))
    details_desc = association_proxy('details', 'details_desc')

Since there are no foreign keys in the schema, you need to tell SQLAlchemy yourself what the join condition for the relationship should be. This is what the remote() and foreign() annotations do.

With that in place, you can use an association_proxy "across" the relationship to create a property on Data which will work the way you want.

Upvotes: 3

Related Questions