DT
Drizzle Team•5mo ago
SunPodder

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.
1 Reply
Sillvva
Sillvva•5mo ago
Try this: 
const result = await db
    .select({
        id: Messages.id,
        text: Messages.text,
        media: sql<
            {
                id: string;
                url: string;
                type: string;
            }[]
        >`COALESCE(json_agg(${Media}) FILTER (WHERE ${Media.id} IS NOT NULL), '[]'::json[])`
        .as("media_items")
    })
    .from(Messages)
    .leftJoin(Media, arrayContains(Messages.media, Media.id))
    .groupBy(Messages.id);
const result = await db
    .select({
        id: Messages.id,
        text: Messages.text,
        media: sql<
            {
                id: string;
                url: string;
                type: string;
            }[]
        >`COALESCE(json_agg(${Media}) FILTER (WHERE ${Media.id} IS NOT NULL), '[]'::json[])`
        .as("media_items")
    })
    .from(Messages)
    .leftJoin(Media, arrayContains(Messages.media, Media.id))
    .groupBy(Messages.id);

Did you find this page helpful?