Richard E Posts - Answer Overflow

Richard E

Explore posts from servers

•Created by Richard E on 3/28/2024 in #help

How can I convert a null value to integer in a select?

I am doing a left join so joined table may contain null fields. In this case I have a player.ranking field that if null I want to default to 9999. I've tried numerous ways but nothing is working:

  const data = await db.select({
    player_name: tournament_snapshot_detail.player_name,
    ranking: sql<number>`ISNULL(${player.ranking},9999)`
  }).from(tournament_snapshot_detail)
    .leftJoin(player, eq(tournament_snapshot_detail.player_name, player.name))
    .where(and(
      eq(tournament_snapshot_detail.snapshot_id, snapshots[0].value ?? 0),
      eq(tournament_snapshot_detail.amateur, false)))
    .orderBy(player.ranking)

  const data = await db.select({
    player_name: tournament_snapshot_detail.player_name,
    ranking: sql<number>`ISNULL(${player.ranking},9999)`
  }).from(tournament_snapshot_detail)
    .leftJoin(player, eq(tournament_snapshot_detail.player_name, player.name))
    .where(and(
      eq(tournament_snapshot_detail.snapshot_id, snapshots[0].value ?? 0),
      eq(tournament_snapshot_detail.amateur, false)))
    .orderBy(player.ranking)

4 replies

DTDrizzle Team

•Created by Richard E on 3/7/2024 in #help

Is there a way to set foreign key on a bulk insert?

Apologies if this is a stupid question but I need to know if I'm missing something obvious. I have two tables where the second one has a foreign key relationship to the other, standard master/detail setup. After I insert the master records, call the entity Pool I return the new id and then I loop through an array of detail records and set that foreign key pool_id to the newly acquired id and then do a batch insert of those detail records PoolTeam like so

async createPoolTeamPlayers(input: CreatePoolTeamPlayer[]): Promise<void> {
    await db.insert(pool_team_player).values(input)
}

async createPoolTeamPlayers(input: CreatePoolTeamPlayer[]): Promise<void> {
    await db.insert(pool_team_player).values(input)
}

Is there any way I can specify the master record id in this insert statement without having to previous loop through the array setting the pool_id property? It it possible to do this master/detail insert in a single call with Drizzle? It works the way I have it but just seems wrong to do it this way.

2 replies

DTDrizzle Team

•Created by Richard E on 1/10/2024 in #help

Is there a way to introspect with specific order?

When I generate my schema file with introspect:pg the tables are not in any order was far as I can see. They are probably in whatever order I created them but it's very messy with a lot of tables. Is there any way to put some kind of order to this?

3 replies

DTDrizzle Team

•Created by Richard E on 12/6/2023 in #help

Infer select that includes a foreign key object collection?

If I have a query such as the following:

const result = await db.query.users.findMany({
  with: {
    posts: true      
  },
});

const result = await db.query.users.findMany({
  with: {
    posts: true      
  },
});

how can I use something like

type SelectUser = InferSelectModel<typeof users>;

type SelectUser = InferSelectModel<typeof users>;

to infer users with posts included?

2 replies

Gaming

Programming