Flask+SQLAlchemy: How to show the latest entry in 1-to-many database

Question

Sorry I couldn't come up with a clear title, I'm not exactly sure what it is called that I want to accomplish. Also english is not my first language so please bear with me.

I've set up a database with two tables "Sensors" and "SensorValues". There should be a one-to-many relationship so that one sensor can have many readings but the readings can belong to only one sensor.

I'm using Flask and flask-sqlalchemy.

Database models:

class Sensor(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    name  = db.Column(db.String(64), unique=True, nullable=False,)
    gpiopin  = db.Column(db.INT, unique=False, nullable=False,)
    stype  = db.Column(db.String(20), unique=False, nullable=False,)
    values = db.relationship('SensorValues', backref='author', lazy=True) #not a column in table, just backreference
    def __repr__(self):
        return f"Sensor('{self.name}', '{self.gpiopin}', '{self.stype}')"

class SensorValues(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    reading = db.Column(db.String(64), unique=False)
    timestamp = db.Column(db.DateTime, unique=False, nullable=False, default=datetime.utcnow)
    sensor_id = db.Column(db.Integer, db.ForeignKey('sensor.id'), nullable=False)
    def __repr__(self):
        return f"Value('{self.reading}', '{self.timestamp}')"

Here's the flask route for home.html:

@app.route("/", methods=['POST', 'GET'])
def home():
  sensors = Sensor.query.all()
  posts = SensorValues.query.all()
  return render_template('home.html', posts=posts, sensors=sensors, db=db)

How I generate the table in home.html

  <tbody>
            {% for sensor in sensors %}
                <tr>
                <td>{{ sensor.name }}</td>
                <td>{{ sensor.gpiopin }}</td>
                <td>{{ sensor.stype }}</td>
                <td>{{ sensor.values.reading }}</td> <- Where I want the latest reading of the specific sensor in the SensorValues to appear
                </tr>
            {% endfor %}
        </tbody>

Now, I have another table which lists all the readings and the names of the sensors they belong to. It's basically a history and this way it works because I'm looping the SensorValues table directly.

  {% for post in posts %}
                <tr>
                <td>{{ post.author.name }}</td>
                <td>{{ post.author.stype }}</td>
                <td>{{ post.reading }}</td>
                <td>{{ post.timestamp }}</td>
                </tr>
  {% endfor %}

I tried to change the first loop to this:

{% for sensor in sensors %}
                <tr>
                <td>{{ sensor.name }}</td>
                <td>{{ sensor.gpiopin }}</td>
                <td>{{ sensor.stype }}</td>
                {{ lastvalue = SensorValues.filter_by(sensor_id=sensor.id).last()}}
                <td>{{ lastvalue.reading }}</td>
                </tr>
{% endfor %}

Where I tried to make a filter query to the SensorValues table with the current sensor.id (the id of the Sensor the for loop is currently in) and logically I think that could work but the syntax is not correct. I got the Jinja error:

expected token 'end of print statement', got '='

Can I even do the query in the home.html or do I have to do it in the routes before the page is rendered?

Answer 1

The query in the view function needs to be updated to include information about the latest reading for the sensors before passing it in the template context.

Start out with writing the query in SQL and then convert it to its SQLAlchemy variant.

The SQL query to find the last sensor reading values is the following:

https://www.db-fiddle.com/f/5fXZyPiPYawc22ffMi2tYk/0

SELECT DISTINCT 
    s.*, 
    FIRST_VALUE(sv.reading) OVER (
  PARTITION BY sv.sensor_id ORDER BY sv.timestamp DESC
) AS last_reading
FROM sensors AS s
JOIN sensor_values AS sv ON s.id = sv.sensor_id

Now, let's build the SQLAlchemy variant

import sqlalchemy as sa

sensors = Sensor.query.join(
              SensorValues).with_entities(
                  Sensor,
                      sa.func.first_value(
                          SensorValues.reading).over(
                              partition_by=SensorValues.sensor_id,
                              order_by=SensorValues.timestamp.desc
                          ).label('latest_reading'))

The Result rows returned from the above query are instances of KeyTuple and the latest_reading label should be available as a direct attribute of the sensor in the Jinja2 template.

{% for sensor in sensors %}
    <tr>
        <td>{{ sensor.Sensor.name }}</td>
        <td>{{ sensor.Sensor.gpiopin }}</td>
        <td>{{ sensor.Sensor.stype }}</td>
        <td>{{ sensor.latest_reading }}</td>
    </tr>
{% endfor %}

Answer 2

I'm not sure if I should edit this to the original post rather than posting it as an answer?

But anyways I got it working with:

     {% for sensor in sensors %}
            <tr>
            <td>{{ sensor.name }}</td>
            <td>{{ sensor.gpiopin }}</td>
            <td>{{ sensor.stype }}</td>
            {% set lastvalue = SensorValues.query.filter_by(sensor_id=sensor.id).order_by(SensorValues.timestamp.desc()).first() %}
            <td>{{ lastvalue.reading }}</td>
            </tr>
        {% endfor %}

And of course I had to add SensorValues to the route:

@app.route("/", methods=['POST', 'GET'])
def home():
  sensors = Sensor.query.all()
  posts = SensorValues.query.all()
  #lastvalue = SensorValues.query.filter_by(sensor_id=sensor.id).last()
  return render_template('home.html', posts=posts, sensors=sensors, db=db, SensorValues=SensorValues

If there is a better way by all means show it, but for now it works.