Dellgenius Posts - Answer Overflow

Dellgenius

Explore posts from servers

•Created by Dellgenius on 5/1/2024 in #help

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!

4 replies

DTDrizzle Team

•Created by Dellgenius on 4/28/2024 in #help

db.select returning [], but a directly executed neon sql statement returns data.

Hi, here is my code.

export const getData = async () => {
    console.log("neon", await sql_neon

SELECT * FROM todo WHERE id = ${1}

);
    console.log("drizzle", await db.select().from(todo));
};

Here is the console output on the server:

neon [ { id: 1, text: 'asdf', done: true } ]
Query: select "id", "text", "done" from "todo"
drizzle []

The second line is from the drizzle debugger. I'm really confused about what's going on. I am using the default code from the nextjs neon todo from the docs. I can successfully insert, but can't select anything. I've been left scratching my head and have no clue why this is happening. Thanks for any help.

1 replies

DTDrizzle Team

•Created by Dellgenius on 4/28/2024 in #help

Error: No database connection string was provided to `neon()`.

Hi. Here is my code:

import { neon } from "@neondatabase/serverless";
import "dotenv/config";
import { drizzle } from "drizzle-orm/neon-http";

const sql = neon(process.env.NEON_DATABASE_URL!);

const db = drizzle(sql);

export default db;

I get the following error: Error: No database connection string was provided to neon(). Perhaps an environment variable has not been set? I have tried hard coding in the string, and it works. I am not sure what's going on, since if I console log the connection string, it shows up just fine in the server log. I tried the solutions here by to no avail: https://discord.com/channels/1043890932593987624/1203696288177070140 Would appreciate any advice!

1 replies

Gaming

Programming