kysely-org / kysely

A type-safe typescript SQL query builder
https://kysely.dev
MIT License
10.71k stars 271 forks source link

Kysely implementation of SELECT EXISTS (SELECT 1 FROM .... WHERE ...) #1078

Closed bbohec closed 3 months ago

bbohec commented 3 months ago

Greetings,

Does kysely support the usage of select exist queries like

SELECT EXISTS (
  SELECT 1 
  FROM table_name 
  WHERE col_name = $1
);

Actually I use a workaround like :

const result = await this.transaction
      .selectFrom("table_name")
      .where("col_name", "=", siret)
      .select((eb) => sql`${eb.fn.count("col_name")} > 0`.as("exist"))
      .executeTakeFirst();
igalklebanov commented 3 months ago

Hey 👋

Yes.

import { sql } from "kysely";

const { exists } = await db
  .selectNoFrom((eb) =>
    eb
      .exists(
        eb
          .selectFrom("table_name")
          .where("col_name", "=", "some_value")
          .select(sql`1` as any),
      )
      .as("exists"),
  )
  .executeTakeFirstOrThrow();

if (exists) {
  // do stuff...
}

https://kyse.link/0TfeB

koskimas commented 3 months ago

Here's a couple more options without as any casts https://kyse.link/2gXq5

In Kysely, you always need to provide an alias for the selections. In my second example, I used _ as the alias. An explicit alias is needed because the column naming logic is complex and dialect-dependent if you don't provide an alias.

bbohec commented 3 months ago

Thanks for you help @igalklebanov & @koskimas . Solution is working as expected.