CODEWITHSUNDEEP
pythonsqlalchemy
Jobu
Jobu

Reputation: 631

How to retrieve a set of objects through multiple levels of SQLAlchemy relationships?

In the following setup how do I retrieve a set of SensorDatas from all of a Car's Tires?

A Car has many Tires and a Tire has one SensorData, so in SQL I'd do something like 

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))

I'm guessing there's a more elegant way to get all the SensorData's than the following, but I can't find it. 

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

Can anyone advise on a better approach? I'm imagining I should be able to do something like

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

    OR

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

Upvotes: 2

Views: 1193

Answers (1)

SuperShoot
SuperShoot

Reputation: 10861

These solutions will return a read only sensor_datas attribute on Car, see comments for more info:

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)

Output:

****************************************************************************************************
[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)]

Upvotes: 4

Related Questions