Hi, I'm evaluating Prisma for work and I have a few questions. I'm switching from Objection JS. The biggest hurdle I see so far is that I can't define a custom relation within the client. Here's an example. Let's say I have these tables:

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

Since

iot_device_status

stores current and historic statuses from every

iot_device

, I use this SQL script to retrieve the current status from every unique device:

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;

In Objection JS, I can define this relationship in my entity and then referenced when needed:

export default class IOTDevice extends BaseEntity {
    ...

    static relationMappings = () => ({
        statusRelationship: {
            relation: Model.HasOneRelation,
            modelClass: IOTDeviceStatus,
            join: {
                from: "iot_device.id",
                to: "iot_device_status.device",
            },
            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"
                            );
                        }
                    );
            },
        },
    });
}

const res = await IOTDevice.query().withGraphJoined("statusRelationship"); // returns the most recent status of each device

but as far as I can tell, there's no support for this kind of modification to relation. I would have to implement this outside of the client. Am I just overlooking something or am I correct in that prisma has no support for something like this?

Hey Anthony! You are correct that Prisma does not support this specific pattern. We are currently exploring more ways to let users extend the Prisma Client, so watch out for that, but in the meantime you would need to get this data by other means, maybe a raw query or utility function.

Thanks for the response. I would imagine there are many scenarios where a simple relationship mapping does not suffice. Is there any guidance currently on how the end user should handle that in Prisma?