Speed Up Sqlalchemy Orm Dynamic Relationship Slicing With Negative Indicies

I have the following SQLA models and relationships. I am logging a measurement for each channel every second, so there are lots of measurements in the DB. class Channel( Model ) :

Solution 1:

You haven't given any ordering, so it has to load all of the objects into a list, and then get the last one.

If you add the echo=True parameter, you can see the difference in the queries:

For measurements[0], it selects just one (LIMIT 1) of the measurements matching the channel:

SELECT measurement.id AS measurement_id, measurement.ts AS measurement_ts,
  measurement.value AS measurement_value,
  measurement.channel_id AS measurement_channel_id
FROM measurement
WHERE %(param_1)s = measurement.channel_id
 LIMIT %(param_2)s
{'param_1': 6, 'param_2': 1}

For measurements[-1], it selects all of the measurements matching the channel. You haven't ordered it, so it has to ask the database to return the rows in whatever order it decides (maybe the primary key on measurement, but no guarantee):

SELECT measurement.id AS measurement_id, measurement.ts AS measurement_ts,  
  measurement.value AS measurement_value,
  measurement.channel_id AS measurement_channel_id
FROM measurement
WHERE %(param_1)s = measurement.channel_id
{'param_1': 6}

If you want just the latest Measurement, select it and order by timestamp field; you probably want indices on channel_id and your timestamp field:

db.session.query(Measurement)\
    .filter(Measurement.channel_id == channel_id)\
    .order_by(Measurement.ts.desc())\
    .limit(1)\
    .first()

Solution 2:

It seems the answer is that efficient slicing or realtionship collections with negative indicies is not supported in SQLA. In fact there seems to be some kludgy attempt in the code but is going to be removed from SQLA as it wasn't carefully thought out.

https://github.com/sqlalchemy/sqlalchemy/issues/5605

I have solved my issue by implementing a hybrid property that returns me the latest measurement, rather than slicing the relationship collection directly.

    @hybrid_propertydeflatest_measurement( self ) -> float :
        """
        Hybrid property that returns the latest measurement for the channel.
        """
        measurement = self.measurements.order_by( Measurement.id.desc() ).first()
        return measurement