How to properly join a table with a raw subquery?

[anthonyma94]

I have 2 tables, one that holds device data, and the other stores status history of the devices:

CREATE TABLE `iot_device` (
  `id` varchar(255) NOT NULL PRIMARY KEY,
  `type` varchar(255) NOT NULL,
  `location` varchar(255) DEFAULT NULL
);

CREATE TABLE `iot_device_status` (
  `id` int(10) UNSIGNED NOT NULL PRIMARY KEY AUTO_INCREMENT,
  `device` varchar(255) NOT NULL,
  `lost` tinyint(1) DEFAULT 0,
  `created_at` datetime(6) DEFAULT current_timestamp(6)
);

This is the SQL I researched to retrieve the latest status from all unique devices:

SELECT * 
FROM iot_device_status s 
JOIN (SELECT device, MAX(created_at) AS max_date FROM iot_device_status GROUP BY device) sm
ON s.created_at = sm.max_date
AND s.device = sm.device;

My problem comes when I'm integrating this to Objection JS:

// This is the table that hold data about each device
export default class IOTDevice extends BaseEntity {
    static tableName = "iot_device";
    id: string;
    type: IOTDeviceTypeEnum;
    location: string;

    static relationMappings = () => ({
        statusRelationship: {
            relation: Model.HasOneRelation,
            modelClass: IOTDeviceStatus,
            join: {
                from: "iot_device.id",
                to: "iot_device_status.device",
            },

            // Problem function
            modify: function (builder) {
                builder
                    .alias("s")
                    .join(
                        IOTDeviceStatus.raw(
                            "(SELECT device, MAX(created_at) AS max_date FROM iot_device_status GROUP BY device) AS sm"
                        ),
                        function () {
                            this.on("s.created_at", "=", "sm.max_date").andOn(
                                "s.device",
                                "=",
                                "sm.device"
                            );
                        }
                    );
            },
        },
    });
}

There's 2 issues I'm having. First, the query doesn't work with withGraphFetched(), only withGraphJoined(). This is the error if I run IOTDevice.query().withGraphFetched("statusRelationship"):

DBError: select `s`.* from `iot_device_status` as `s` inner join (SELECT device, MAX(created_at) AS 
max_date FROM iot_device_status GROUP BY device) AS sm on `s`.`created_at` = `sm`.`max_date` and 
`s`.`device` = `sm`.`device` where `iot_device_status`.`device` in ('000000005bce7983', 
'00000000a38caa9c', '00D0C9E47E98', '00D0C9E47E99', '00D0C9E47E9E', '00D0C9E47EAA', 
'00D0C9E47EAF', '00D0C9E47EED', '00D0C9E47EF1', '00D0C9E47EF2', '10000000a805e4d', 
'Weintek-CMTx04', 'Weintek-CMTx05', 'Weintek-CMTx06', 'Weintek-CMTx90') - Unknown column 
'iot_device_status.device' in 'where clause'

Second, even with withGraphJoined(), I can't use any functions that reference an ambiguous column. For example, this is the error message if I run IOTDevice.query().withGraphJoined("statusRelationship"):

ConstraintViolationError: select `iot_device`.`id` as `id`, `iot_device`.`type` as `type`, `iot_device`.`location`
 as `location`, `statusRelationship`.`id` as `statusRelationship:id`, `statusRelationship`.`device` as 
`statusRelationship:device`, `statusRelationship`.`lost` as `statusRelationship:lost`, 
`statusRelationship`.`created_at` as `statusRelationship:created_at` from `iot_device` left join (select `s`.* 
from `iot_device_status` as `s` inner join (SELECT device, MAX(created_at) AS max_date FROM 
iot_device_status GROUP BY device) AS sm on `s`.`created_at` = `sm`.`max_date` and `s`.`device` = 
`sm`.`device`) as `statusRelationship` on `statusRelationship`.`device` = `iot_device`.`id` where `id` = 
'00D0C9E47EF1' - Column 'id' in where clause is ambiguous

I'd like to be able to run the latter function without having to reference the table. Any suggestions on how I can fix this?