Drizzle TeamDT
Drizzle Team•2y ago
Dellgenius

How can I get a better structured response when querying a table (many to many)?

Hi, I am new to sql. This might be obvious, but I'm not sure how to go about this. Below is a simplified example of what I hope to achieve.

I have three tables: orders, order_lines, and items. order_lines is a junction table. Here's how I'm currently fetching the data:

const ordersWithItems = await db.select().from(orders)
    .leftJoin(order_lines, eq(order_lines.order_id, orders.xata_id))
    .leftJoin(items, eq(items.xata_id, order_lines.item_id));


This results in a response like this:

[
    {
        "order_id": "order1",
        "order_date": "2024-04-01",
        "line_id": "line1",
        "item_id": "item1",
        "item_price": "1000"
    },
    {
        "order_id": "order1",
        "order_date": "2024-04-01",
        "line_id": "line2",
        "item_id": "item2",
        "item_price": "50"
    }
]


Desired Output:
I want to structure the JSON to group order_lines and items under each order:

[
    {
        "order_id": "order1",
        "order_date": "2024-04-01",
        "lines": [
            {
                "line_id": "line1",
                "price": "1000",
                "item": {
                    "id": "item1",
                    "name": "Laptop",
                }
            },
            {
                "line_id": "line2",
                "price": "50",
                "item": {
                    "id": "item2",
                    "name": "Mouse",
                }
            }
        ]
    }
]


Is there a way to to do this natively with drizzle, or do I need to manually process the results after fetching them? Would appreciate any help!
Was this page helpful?