Drizzle Team•7mo ago

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

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"
    }
]

[
    {
        "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",
                }
            }
        ]
    }
]

[
    {
        "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!

3 Replies

arily•7mo ago

you can if you can use https://orm.drizzle.team/docs/rqb to query your data?

Drizzle ORM - Query

Drizzle ORM is a lightweight and performant TypeScript ORM with developer experience in mind.

ZAZ•7mo ago

is there a difference in use case for db.query vs. the partial selects? i'm also trying to figure out the same issue.

arily•7mo ago

db.query have ORM DX and it’ll creates SQL automatically, while selects are simple query builders

Gaming

Programming

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