python - 如何通过多个级别的SQLAlchemy关系检索一组对象？

在以下设置中，如何从所有汽车轮胎中检索一组SensorData？

汽车有很多轮胎，轮胎有一个SensorData，因此在SQL中，我会做类似

SELECT sd.*
FROM Car AS c
    INNER JOIN Tire AS t ON c.id = t.car_id
    INNER JOIN SensorData AS sd ON t.id = sd.tire_id

class Car(Base):
    pass

class Tire(Base):
    car = relationship("Car", backref="tires")

class SensorData(Base):
    tire = relationship("Tire", backref=backref("sensor_data", uselist=False))

我猜想有一种比以下方法更优雅的方法来获取所有SensorData，但我找不到它。

tires = Car.query.get(1).tires
sensor_datas = [sensor_data for tire.sensor_data in tires]

谁能建议更好的方法？我在想我应该能够做类似的事情

sensor_datas = Car.query.get(1).sensor_datas

    OR

sensor_datas = Car.query.get(1).tires.sensor_data

最佳答案

这些解决方案将在sensor_datas上返回只读的Car属性，有关更多信息，请参见注释：

class Car(Base):
    id = Column(Integer, primary_key=True)

    # results in a collection of sensor_data objects. This method is the
    # cleanest reading by far, but behaves a little differently from the
    # other two in terms of how the collection is constructed. Accessing
    # the association proxy causes sqlalchemy to query the tire table for
    # for the `Car`'s `Tire`s and then iterates over each one, accessing
    # its `sensor_data` attribute to construct the collection on `Car`.
    # With the lazy loading defaults, this resulted in 5 queries to
    # construct the relationship, so depending on how you need to use it
    # you might consider some sort of eager loading.
    sensor_datas_aprox = association_proxy('tires', 'sensor_data')

    # Joins Tire and SensorData. TBH the docs only mention
    # using secondary in context of m:n relationship, but
    # this seems to work OK.
    sensor_datas_rel = relationship(
        'SensorData',
        secondary='join(Tire, SensorData)',
        viewonly=True
    )

    # a query enabled property, queries for the data on
    # access, pretty self explanatory.
    @property
    def sensor_datas_prop(self):
        return object_session(self).query(SensorData).\
            join(Tire).filter(
                Tire.car_id == self.id,
                SensorData.tire_id == Tire.id
            ).all()


class Tire(Base):
    id = Column(Integer, primary_key=True)
    car_id = Column(Integer, ForeignKey('car.id'))
    car = relationship("Car", backref="tires")


class SensorData(Base):
    tire_id = Column(Integer, ForeignKey('tire.id'), primary_key=True)
    tire = relationship("Tire", backref=backref("sensor_data", uselist=False))


if __name__ == '__main__':
    s = Session()
    for _ in range(2):
        car = Car()
        car.tires = [Tire(sensor_data=SensorData()) for _ in range(4)]
        s.add(car)
    s.commit()
    for car in s.query(Car):
        print('*' * 100)
        print(car.sensor_datas_rel)
        print(car.sensor_datas_prop)
        print(car.sensor_datas_aprox)

输出：

****************************************************************************************************
[SensorData(tire_id=1), SensorData(tire_id=2), SensorData(tire_id=3), SensorData(tire_id=4)]
[SensorData(tire_id=1), SensorData(tire_id=2), SensorData(tire_id=3), SensorData(tire_id=4)]
[SensorData(tire_id=1), SensorData(tire_id=2), SensorData(tire_id=3), SensorData(tire_id=4)]
****************************************************************************************************
[SensorData(tire_id=5), SensorData(tire_id=6), SensorData(tire_id=7), SensorData(tire_id=8)]
[SensorData(tire_id=5), SensorData(tire_id=6), SensorData(tire_id=7), SensorData(tire_id=8)]
[SensorData(tire_id=5), SensorData(tire_id=6), SensorData(tire_id=7), SensorData(tire_id=8)]

关于python - 如何通过多个级别的SQLAlchemy关系检索一组对象？，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/56070657/