Kysely•11mo ago

where array_field has enum value

Hello there 👋 Today I'm experimenting with kysely, coming from prisma. I got stuck on rewriting the first query facePalm

I have a ShippingMethod table with stores field, which is array of Store enum

export const Store = {
  PUBLIC: 'PUBLIC',
  RETAIL: 'RETAIL',
  WHOLESALE: 'WHOLESALE',
} as const;

export const Store = {
  PUBLIC: 'PUBLIC',
  RETAIL: 'RETAIL',
  WHOLESALE: 'WHOLESALE',
} as const;

I have this prisma query, filtering shipping methods which have Store.PUBLIC in the stores array field:

    const shippingMethods = await this.prismaService.shippingMethod.findMany({
      where: {
        stores: { has: Store.PUBLIC },
        minAmount: 0,
        minWeight: 0,
      },
    });

    const shippingMethods = await this.prismaService.shippingMethod.findMany({
      where: {
        stores: { has: Store.PUBLIC },
        minAmount: 0,
        minWeight: 0,
      },
    });

Could anyone please guide me on how to achieve the same with kysely and postgresql dialect? I've found this can be achieved in postgresql using ANY(stores), but I wasn't able to build the query (https://commandprompt.com/education/how-to-check-if-postgresql-array-contains-a-value/). Thanks in advance! 🙏

Solution:

That's currently ~the best way to do it unfortunately. Here's a more type-safe approach: ```ts const shippingMethods2 = await this.dbService.db .selectFrom('ShippingMethod')...

Jump to solution

4 Replies

wod•11mo ago

I think I got it working:

    const shippingMethods2 = await this.dbService.db
      .selectFrom('ShippingMethod')
      .where(sql.val(DBStore.PUBLIC), '=', sql`ANY(${sql.ref('stores')})`)
      .where('minAmount', '>=', 0)
      .where('minWeight', '>=', 0)
      .selectAll()
      .execute();

    const shippingMethods2 = await this.dbService.db
      .selectFrom('ShippingMethod')
      .where(sql.val(DBStore.PUBLIC), '=', sql`ANY(${sql.ref('stores')})`)
      .where('minAmount', '>=', 0)
      .where('minWeight', '>=', 0)
      .selectAll()
      .execute();

Solution

koskimas•11mo ago

That's currently ~the best way to do it unfortunately. Here's a more type-safe approach:

const shippingMethods2 = await this.dbService.db
  .selectFrom('ShippingMethod')
  .where(eb =>
     eb(eb.val(DBStore.PUBLIC), '=', eb.fn('any', ['stores']))
  )
  .where('minAmount', '>=', 0)
  .where('minWeight', '>=', 0)
  .selectAll()
  .execute();

const shippingMethods2 = await this.dbService.db
  .selectFrom('ShippingMethod')
  .where(eb =>
     eb(eb.val(DBStore.PUBLIC), '=', eb.fn('any', ['stores']))
  )
  .where('minAmount', '>=', 0)
  .where('minWeight', '>=', 0)
  .selectAll()
  .execute();

wod•11mo ago

Thank you ❤️

koskimas•11mo ago

I'm currently adding the any function to the function module. With the next Kysely version you'll be able to say

const shippingMethods2 = await this.dbService.db
  .selectFrom('ShippingMethod')
  .where(({ eb, val, fn }) =>
     eb(val(DBStore.PUBLIC), '=', fn.any('stores')))
  )
  .where('minAmount', '>=', 0)
  .where('minWeight', '>=', 0)
  .selectAll()
  .execute();

const shippingMethods2 = await this.dbService.db
  .selectFrom('ShippingMethod')
  .where(({ eb, val, fn }) =>
     eb(val(DBStore.PUBLIC), '=', fn.any('stores')))
  )
  .where('minAmount', '>=', 0)
  .where('minWeight', '>=', 0)
  .selectAll()
  .execute();

fn.any only accepts array columns and automatically checks the value type against the left operand.

Gaming

Programming

where array_field has enum value