Drizzle Team•2y ago

How can I declare a relationship with a SELECT statement?

I have 2 tables, iot_device and 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;

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

6 Replies

xamarot•2y ago

I don't think you can do that. I think what you want is actually a View

azn4lifeeOP•2y ago

I could turn it into a view, but the relations field doesn't accept a view either

xamarot•2y ago

Why do you have to put it as relations?

rodrigo•2y ago

I'm also interesting in inserting a view in a relation and I see it's not possible? Is there any workaround? Would it be implemented?

azn4lifeeOP•17mo ago

Because it helps tremendously with DX. The current status will always be needed in our app when we query iot device

Asseater Peter•17mo ago

I'm not home so I can't make one specifically to answer your question, but here's a snippet of code I already sent somewhere on Discord which can pretty trivially be changed to fit your needs:

db.query.UserTable.findMany({
      with: {
        sent: {
          where: (message, { eq }) => eq(message.recipient_id, UserTable.id),
          orderBy: (message, { desc }) => desc(message.timestamp),
          limit: 1,
        },
      },
    })

db.query.UserTable.findMany({
      with: {
        sent: {
          where: (message, { eq }) => eq(message.recipient_id, UserTable.id),
          orderBy: (message, { desc }) => desc(message.timestamp),
          limit: 1,
        },
      },
    })

Relations:

export const MessageRelations = relations(MessageTable, ({ one }) => ({
  sender: one(UserTable, {
    fields: [MessageTable.sender_id],
    references: [UserTable.id],
    relationName: 'MessageSender',
  }),

  recipient: one(UserTable, {
    fields: [MessageTable.recipient_id],
    references: [UserTable.id],
    relationName: 'MessageReceiver',
  }),
}))

export const UserRelations = relations(UserTable, ({ many }) => ({
  sent: many(MessageTable, { relationName: 'MessageSender' }),
  received: many(MessageTable, { relationName: 'MessageReceiver' }),
}))

export const MessageRelations = relations(MessageTable, ({ one }) => ({
  sender: one(UserTable, {
    fields: [MessageTable.sender_id],
    references: [UserTable.id],
    relationName: 'MessageSender',
  }),

  recipient: one(UserTable, {
    fields: [MessageTable.recipient_id],
    references: [UserTable.id],
    relationName: 'MessageReceiver',
  }),
}))

export const UserRelations = relations(UserTable, ({ many }) => ({
  sent: many(MessageTable, { relationName: 'MessageSender' }),
  received: many(MessageTable, { relationName: 'MessageReceiver' }),
}))

Note: as we've figured out today, this only works on many-to-x relations and you can only use the where clause on the many side i.e. user -> messages but not message -> sender

Gaming

Programming

How can I declare a relationship with a SELECT statement?