DT
Drizzle TeamDellgenius

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!
arily
arily•17d 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
ZAZ•17d 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
arily•16d ago
db.query have ORM DX and it’ll creates SQL automatically, while selects are simple query builders
Want results from more Discord servers?
Add your server
More Posts
sql where column md5Hey, how can I achieve the following statement in drizzle? ```sql -- mysql SELECT * FROM podcasts_Error on Query Filter: Types of property 'table' are incompatible.I'm currently trying to filter a query as [explained in on the docs](https://orm.drizzle.team/docs/rHow to do Computed Fields / Manipulate data at Query Level?I'm looking for something like this, on prisma we can validate fields when querying the model. I coupgEnum conversion not working as expectedI have the following pgEnum defined: ``` export const ApprovalRequestTypePgEnum = pgEnum('approval_Filtering a jsonb with the shape Array<{id:string,name:string}>Hi. I have a query that returns all the posts with it's categories like this, ```typescript type PosCan't update table columnI update the column's name in my schema, but it don't update when I check in Drizzle Studio. AnyoneRunning an insert script, and I'm getting some "warnings".How do you access the warning details or what rows they relate to?drizzle-orm/libsql in cf workers causes type conflictsglobal fetch returns node Response type instead of Response type from @cloudflare/workers-types when