How can I declare a relationship with a SELECT statement?

I have 2 tables,

iot_device

iot_device

iot_device

iot_device and

iot_device_status

iot_device_status

iot_device_status

iot_device_status, where the status table saves all current and historical statuses of each device. I like to join the tables together as so:

SELECT i.id, i.name, data.lost, data.created_at FROM iot_device i
    INNER JOIN LATERAL (
        SELECT * FROM iot_device_status s
        WHERE s.device = i.id
        ORDER BY created_at DESC LIMIT 1
    ) AS data
ON true
ORDER BY i.id, data.created_at DESC;

SELECT i.id, i.name, data.lost, data.created_at FROM iot_device i
    INNER JOIN LATERAL (
        SELECT * FROM iot_device_status s
        WHERE s.device = i.id
        ORDER BY created_at DESC LIMIT 1
    ) AS data
ON true
ORDER BY i.id, data.created_at DESC;

SELECT i.id, i.name, data.lost, data.created_at FROM iot_device i
    INNER JOIN LATERAL (
        SELECT * FROM iot_device_status s
        WHERE s.device = i.id
        ORDER BY created_at DESC LIMIT 1
    ) AS data
ON true
ORDER BY i.id, data.created_at DESC;

SELECT i.id, i.name, data.lost, data.created_at FROM iot_device i
    INNER JOIN LATERAL (
        SELECT * FROM iot_device_status s
        WHERE s.device = i.id
        ORDER BY created_at DESC LIMIT 1
    ) AS data
ON true
ORDER BY i.id, data.created_at DESC;

I noticed

relations

relations

relations

relations only take in a pgTable. How can I make it so that it takes the result of the given SQL statement instead?

How can I declare a relationship with a SELECT statement?

Similar Threads

Similar Threads

Similar Threads