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 👇🏿

9 Replies

PillowOP•15mo ago

Uh, i fixed it ;D

PillowOP•15mo ago

but this thing is kinda bad.

Angelelz•15mo ago

What is bad?

PillowOP•15mo ago

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

PillowOP•15mo 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•15mo 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•15mo 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

PillowOP•15mo ago

yeap, thx ❤️

Gaming

Programming

Query in drizzle doesn't work