How to do a leftJoin on a pgArray of uuid?
export const Messages = pgTable("messages", {
id: uuid("id").defaultRandom().primaryKey(),
text: text("text").notNull(),
media: uuid("media_id")
.references(() => Media.id)
.array()
.default(sql`'{}'::uuid[]`),
});
export const Media = pgTable("media", {
id: uuid("id").defaultRandom().primaryKey(),
url: text("url").notNull(),
type: text("type").notNull(),
});export const Messages = pgTable("messages", {
id: uuid("id").defaultRandom().primaryKey(),
text: text("text").notNull(),
media: uuid("media_id")
.references(() => Media.id)
.array()
.default(sql`'{}'::uuid[]`),
});
export const Media = pgTable("media", {
id: uuid("id").defaultRandom().primaryKey(),
url: text("url").notNull(),
type: text("type").notNull(),
});How do I create a join on the Messages table to get the associated Media data with it?
for example let's say i have the following data
**Messages**
--------------------------------------------------------
| id | text | media |
--------------------------------------------------------
| "message1" | "Hello" | ["media1", "media2"] |
--------------------------------------------------------
**Media**
---------------------------------------------
| id | url | type |
---------------------------------------------
| "media1" | "/profile.png" | "image/png" |
| "media2" | "/demo.gif" | "image/gif" |
---------------------------------------------**Messages**
--------------------------------------------------------
| id | text | media |
--------------------------------------------------------
| "message1" | "Hello" | ["media1", "media2"] |
--------------------------------------------------------
**Media**
---------------------------------------------
| id | url | type |
---------------------------------------------
| "media1" | "/profile.png" | "image/png" |
| "media2" | "/demo.gif" | "image/gif" |
---------------------------------------------I want the final result to be something like this:
[
{
"id": "message1",
"text": "Hello",
"media": [
{
"id": "media1",
"url": "/profile.png",
"type": "image/png"
},
{
"id": "media2",
"url": "/demo.gif",
"type": "image/gif"
},
]
}
][
{
"id": "message1",
"text": "Hello",
"media": [
{
"id": "media1",
"url": "/profile.png",
"type": "image/png"
},
{
"id": "media2",
"url": "/demo.gif",
"type": "image/gif"
},
]
}
]The data structure might be different, that's not a problem. But I'm looking for how to fetch everything in 1 query.
