CODEWITHSUNDEEP
pythonsqlalchemy
ratatatat
ratatatat

Reputation: 152

SQLAlchemy hybrid expression access to different row

I have the following datamodel:

class TS(Base):  # Parent
    __tablename__ = 'ts'
    id = Column(Integer, primary_key=True)
    inspection_data = relationship('ID', backref='ts', uselist=False)

and 

class ID(Base):  # Child
    __tablename__ = 'ins' 
    id = Column(Integer, primary_key=True)
    created = Column(Date, default=datetime.datetime.utcnow(), nullable=False)
    next_inspection = Column(Date, unique=False, nullable=True)
    ts_id = Column(Integer, ForeignKey('ts.id'))

Now I would like to create a hybrid property 'status' that tells me if the next_inspection is overdue (i.e. behind today)

I solved that so far as:

    @hybrid_property
    def status(self):
        now = datetime.datetime.utcnow().date()
        delta = self.next_inspection - now
        if delta.days > 30:
            return 3  # OK, next inspection far away
        elif delta.days > 0:
            return 2  # Next inspection getting close
        else:  # We are overdue
            return 1

This works both at class as on instance level, meaning I can even query using status. In order to work I had to add:

    @status.comparator
    def status(cls):
        return DateTimeComparator(cls.next_inspection)

with

class DateTimeComparator(Comparator):
    def __gt__(self, other):
        return self.__clause_element__() > other
    # same for __lt__, __eq__ etc

Now to my question: Instead of returning as in above example the values 1, 2 or 3 for the status, I want to return one of three values that are stored in another table with the following model:

class IDStatusValues(Base):
    __tablename__ = 'ins_status_values'
   id = Column(Integer, primary_key=True)
   value = Column(Unicode, unique=True, nullable=True, info={})
   ID_id = Column(Integer, ForeignKey('ins.id'))

with the following data:

id | value
-----------
0  | 'OK'
1  | 'APPROACHING'
2  | 'OVERDUE'

That is I would like to change the status method to something like this:

   @hybrid_property
    def status(self):
        now = datetime.datetime.utcnow().date()
        delta = self.next_inspection - now
        if delta.days > 30:
            return IDStatusValues.get(0)  # OK, next inspection far away
        elif delta.days > 0:
            return IDStatusValues.get(1)  # Next inspection getting close
        else:  # We are overdue
            return IDStatusValues.get(2)

But I do not understand how I could query inside this function. I admit until now I only worked in declarative and am not very experienced in true SQL / expressive sqlalchemy.

Upvotes: 2

Views: 580

Answers (1)

Ameet S M
Ameet S M

Reputation: 190

class TS(Base):  # Parent
    __tablename__ = 'ts'
    id = Column(Integer, primary_key=True)
    inspection_data = relationship('ID', backref='ts', uselist=False)`

class ID(Base):  # Child
    __tablename__ = 'ins'
    id = Column(Integer, primary_key=True)
    created = Column(Date, default=datetime.datetime.utcnow(), nullable=False)
    next_inspection = Column(Date, unique=False, nullable=True)
    ts_id = Column(Integer, ForeignKey('ts.id'))
    IDStatusValues = relationship('IDStatusValues') **# Add this relationship**

    @hybrid_property
    def status(self):
        now = datetime.datetime.utcnow().date()
        delta = self.next_inspection - now
        if delta.days > 30:
            return self.IDStatusValues.property.table.c.id == 0  # OK, next inspection far away
        elif delta.days > 0:
            return self.IDStatusValues.property.table.c.id == 1  # Next inspection getting close
        else:  # We are overdue
            return self.IDStatusValues.property.table.c.id == 2`

class IDStatusValues(Base):
    __tablename__ = 'ins_status_values'
    id = Column(Integer, primary_key=True)
    value = Column(Unicode, unique=True, nullable=True, info={})
    ID_id = Column(Integer, ForeignKey('ins.id'))`

session.query(ID).filter(ID.status).all()

Upvotes: 1

Related Questions