lewisedc Posts - Answer Overflow

lewisedc

•Created by lewisedc on 1/7/2025 in #help

Is there any way to have a subquery in an insert statement?

I want to use drizzle to replicate this sql:

INSERT INTO games (name, sortOrder)
VALUES ('New Game', COALESCE((SELECT MAX(sortOrder) FROM games), 0) + 1);

INSERT INTO games (name, sortOrder)
VALUES ('New Game', COALESCE((SELECT MAX(sortOrder) FROM games), 0) + 1);

However I don't know if there's a way to have a subquery in an insert statement like above. I've tried:

await db.insert(games).values({
  name: 'New Game',
  sortOrder: db.select({ sortOrder: sql<number>`coalesce(${max(gamesTable.sortOrder)}, 0) + 1`
});

await db.insert(games).values({
  name: 'New Game',
  sortOrder: db.select({ sortOrder: sql<number>`coalesce(${max(gamesTable.sortOrder)}, 0) + 1`
});

But this doesn't work, can anyone shed some insight please?

4 replies

DTDrizzle Team

•Created by lewisedc on 12/3/2024 in #help

What's the recommended way to handle joins that aggregate results?

I'm currently converting a codebase that uses raw sql postgres queries over to drizzle, and have this query:

SELECT
  games.name,
  games.image_url,
  JSON_AGG(
    JSON_BUILD_OBJECT(
      'name', game_features.name,
      'icon_url', game_features.icon_url
    )
  ) AS features
FROM
  games
  LEFT JOIN game_features ON games.id = game_features.game_id
GROUP BY
  games.name,
  games.image_url,
  games.sort_order
ORDER BY
  games.sort_order;

SELECT
  games.name,
  games.image_url,
  JSON_AGG(
    JSON_BUILD_OBJECT(
      'name', game_features.name,
      'icon_url', game_features.icon_url
    )
  ) AS features
FROM
  games
  LEFT JOIN game_features ON games.id = game_features.game_id
GROUP BY
  games.name,
  games.image_url,
  games.sort_order
ORDER BY
  games.sort_order;

I was wondering what the recommended way of queries that aggregate joins using functions such as JSON_AGG or ARRAY_AGG?

6 replies

DTDrizzle Team

•Created by lewisedc on 11/21/2024 in #help

Is this query possible using the Query API?

I have this query:

db.query.usersTable.findFirst({ with: { userDetails: true }, where: eq(userDetailsTable.email, email) });

db.query.usersTable.findFirst({ with: { userDetails: true }, where: eq(userDetailsTable.email, email) });

but when ran it throws the error column usersTable.email does not exist even though I'm trying to filter based on userDetailsTable. Is this query not possible using the Query API or am I just doing it incorrectly?

6 replies

Gaming

Programming