Query in drizzle doesn't work

Error:

DrizzleError: No fields selected for table "usersToGroups" ("users_groups")

DrizzleError: No fields selected for table "usersToGroups" ("users_groups")

Drizzle query builder

    await db
        .select({
            id: users.id,
            name: users.name,
            groups: sql`json_agg(jsonb_build_object('id', ${groups.id}, 'name', ${groups.name}))`,
        })
        .from(users)
        .leftJoin(usersToGroups, eq(users.id, usersToGroups.userId))
        .leftJoin(groups, eq(groups.id, usersToGroups.groupId))
        .groupBy(users.id, users.name, usersToGroups.userId)
        .where(eq(users.id, newUser.id));

    await db
        .select({
            id: users.id,
            name: users.name,
            groups: sql`json_agg(jsonb_build_object('id', ${groups.id}, 'name', ${groups.name}))`,
        })
        .from(users)
        .leftJoin(usersToGroups, eq(users.id, usersToGroups.userId))
        .leftJoin(groups, eq(groups.id, usersToGroups.groupId))
        .groupBy(users.id, users.name, usersToGroups.userId)
        .where(eq(users.id, newUser.id));

SQL query

SELECT
    u.id,
    u.name,
    json_agg(jsonb_build_object('id', g.id, 'name', g.name)) AS groups
FROM users u
LEFT JOIN users_to_groups utg ON u.id = utg.user_id
LEFT JOIN groups g ON utg.group_id = g.id
GROUP BY u.id, u.name, utg.user_id;

SELECT
    u.id,
    u.name,
    json_agg(jsonb_build_object('id', g.id, 'name', g.name)) AS groups
FROM users u
LEFT JOIN users_to_groups utg ON u.id = utg.user_id
LEFT JOIN groups g ON utg.group_id = g.id
GROUP BY u.id, u.name, utg.user_id;

pgsql cli output 👇🏿

Pillow•248d ago

Uh, i fixed it ;D

Pillow•248d ago

but this thing is kinda bad.

Angelelz•248d ago

What is bad?

Pillow•248d ago

If the user has zero groups, i won't get an empty array, but i'd like to 😭

Pillow•248d ago

      COALESCE(
        json_agg(json_build_object('id', g.id, 'name', g.name)),
        '[]'::json
      )

      COALESCE(
        json_agg(json_build_object('id', g.id, 'name', g.name)),
        '[]'::json
      )

I tried do smth like this, but.. https://orm.drizzle.team/docs/joins#aggregating-results Maybe I just need to aggregate the results within the js After prisma sql-like seems quite complicated, but more efficient.

Angelelz•248d ago

json_agg(case when g.id IS NULL then null else json_build_object('id', g.id, 'name', g.name) then)

json_agg(case when g.id IS NULL then null else json_build_object('id', g.id, 'name', g.name) then)

I'm not sure this will produce [] or [null]

Angelelz•248d ago

This is another way https://stackoverflow.com/questions/24155190/postgresql-left-join-json-agg-ignore-remove-null

Stack Overflow

Postgresql LEFT JOIN json_agg() ignore/remove NULL

I am using a LEFT JOIN there will be cases where there is no right-table match therefore empty (null) values are substituted for the right-table columns. As a result I am getting [null] as one of the

Pillow•248d ago

yeap, thx ❤️

Drizzle Team Join

The official Discord for all Drizzle related projects, such as Drizzle ORM, Drizzle Kit, Drizzle Studio and more!

7.4KMembers

View on Discord

Want results from more Discord servers?

Add your server

Cannot read properties of undefined (reading 'referencedTable')Seemingly getting this error out of nowhere. I believe it's related to the relations I defined, but Relational query builder in mysql?I cant find anything about how to implement this. Am I blind or is this impossible?TransactionRollbackErrorNeed some help understanding TransactionRollbackError... Is this error saying the `tx.rollback()` f PostgresError: null value in column "id" of relation "pairs" violates not-null constraintHello, I am fairly new to Drizzle and working with databases, so I am running into a problem that I OnConflictDoNothing with mysqlHi everyone, I want to use the on conflict do nothing method described here: https://orm.drizzle.tea drizzle-kit introspect reorder tablesHere is the case: - I have a remote database. I made a backup and restore it locally - I ran: drizz Migration custom loggerHey there, I'm want to run migration as part of my CI/CD, and would have liked the output of the `mi why do snapshots not have the same name as the migrations?Hey, loving dirzzle so far. Hit a snag recently. Me and my friend are collaborating on a project. Help with improving database queryHello, I am seeking advice on how to improve a database query I am currently working with. My object Typescript types for relational queries with neon dbHey, I am having an issue with typescript types for relational queries with drizzle and neon db. Det .onUpdateNow() for postgresql?is that possible?Migrating in JS & JSON (no filesytem)👋 we're big fans (and sponsors) of Drizzle at Val Town. We use it internally and love it. We just onDuplicateKeyUpdatei'm having problems using `onDuplicateKeyUpdate`, doing it like ```js // values is an array of e Unable to generate uuid as primary keyHey everyone! I've searched through the help posts with similar titles but I could not get the answe [Has Solution] Drizzle Studio says it's running on the server http://0.0.0.0Idk what might be the issue. But when I run `pnpm drizzle-kit studio`. I am receiving the message: `Error using relational queries but not with core querieswhen i do ``` const invitesData = await db.select().from(invites).innerJoin(members, eq(invites.in Is it good to use UUID as primary key ?Hi, i want to define manually an id for my entry to be able to create at tame time a folder with sam What exactly i am doing wrong.. Prepared Query```json [{ "error": { "json": { "message": "prepared statement \"preparedCon Upsert create/update joined table similar to PrismaHey all, trying to get another one of my queries to migrate away from prisma. This one is a bit more dynamic table namehow is that my code breaks: ```ts const { tableName } = params; const data = await dbPool.execute(sq

Gaming

Programming

Query in drizzle doesn't work