irelynx Comments - Answer Overflow

irelynx

•Created by irelynx on 1/15/2024 in #help

Weird Query builder behavior (extras)

I am not sure how exactly it works under the hood, but all "queries" that you await - will be executed and you will receive an execution result, but if you will not await - it can be used as a subquery later in your code. I am not sure though is it possible to store them separately or not (like, create special file with all subqueries and import one of them then it needed)

24 replies

DTDrizzle Team

•Created by irelynx on 1/15/2024 in #help

Weird Query builder behavior (extras)

I am not sure, but it can happens because of

const numOfDevs = await... // you are receiving result here!

const numOfDevs = await... // you are receiving result here!

Try to remove await, and check again

24 replies

DTDrizzle Team

•Created by irelynx on 1/15/2024 in #help

Weird Query builder behavior (extras)

yeap, everything is fine now.

24 replies

DTDrizzle Team

•Created by irelynx on 1/15/2024 in #help

Weird Query builder behavior (extras)

😀

24 replies

DTDrizzle Team

•Created by irelynx on 1/15/2024 in #help

Weird Query builder behavior (extras)

I will try it out on bigger query and write down if everything is fine or not. Thanks again for your help!

24 replies

DTDrizzle Team

•Created by irelynx on 1/15/2024 in #help

Weird Query builder behavior (extras)

oh, finally! i changed your code a little bit and now it produces valid SQL!

const sq = db.select({ avgPrice: pricesMinMaxView.avg_price })
      .from(pricesMinMaxView)
      .where(eq(pricesMinMaxView.productId, products.id))
      .limit(1)
//    .as("avgPrice") // remove

db.query.products.findMany({
  columns: {
    id: true,
  },
  extras: {
//  avgPrice: sql<number>`${sq.avgPrice}`.as("avgPrice")
    avgPrice: sql<number>`${sq}`.as("avgPrice")
  },
  limit: 10
})

const sq = db.select({ avgPrice: pricesMinMaxView.avg_price })
      .from(pricesMinMaxView)
      .where(eq(pricesMinMaxView.productId, products.id))
      .limit(1)
//    .as("avgPrice") // remove

db.query.products.findMany({
  columns: {
    id: true,
  },
  extras: {
//  avgPrice: sql<number>`${sq.avgPrice}`.as("avgPrice")
    avgPrice: sql<number>`${sq}`.as("avgPrice")
  },
  limit: 10
})

produced SQL:

select "id", (select "avg_price" from "latest_prices_minmax" where "latest_prices_minmax"."productId" = "products"."id" limit $1) as "avgPrice" from "products" limit $2

select "id", (select "avg_price" from "latest_prices_minmax" where "latest_prices_minmax"."productId" = "products"."id" limit $1) as "avgPrice" from "products" limit $2

24 replies

DTDrizzle Team

•Created by irelynx on 1/15/2024 in #help

Weird Query builder behavior (extras)

now generated query looks like this:

select "id", "avg_price" as "avgPrice" from "products" where "products"."id" = $1 limit $2

select "id", "avg_price" as "avgPrice" from "products" where "products"."id" = $1 limit $2

24 replies

DTDrizzle Team

•Created by irelynx on 1/15/2024 in #help

Weird Query builder behavior (extras)

yeah, sorry for awkward naming

24 replies

DTDrizzle Team

•Created by irelynx on 1/15/2024 in #help

Weird Query builder behavior (extras)

what do you mean by this message?

24 replies

DTDrizzle Team

•Created by irelynx on 1/15/2024 in #help

Weird Query builder behavior (extras)

i tried to remove .as("avgPrice").avgPrice and added .getSQL().as('avgPrice'), then query builds, but it looks like this:

select "id", select "avg_price" from "latest_prices_minmax" where "products"."productId" = "products"."id" limit $1 as "avgPrice" from "products" where "products"."id" = $2 limit $3

select "id", select "avg_price" from "latest_prices_minmax" where "products"."productId" = "products"."id" limit $1 as "avgPrice" from "products" where "products"."id" = $2 limit $3

24 replies

DTDrizzle Team

•Created by irelynx on 1/15/2024 in #help

Weird Query builder behavior (extras)

Thanks for your answer, but unfortunately it didn't help much: 1. TypeScipt linter arguing:

Type 'PgColumn<{ name: "avg_price"; tableName: "avgPrice"; dataType: "number"; columnType: "PgReal"; data: number; driverParam: string | number; notNull: false; hasDefault: false; enumValues: undefined; baseColumn: never; }, {}, {}>' is missing the following properties from type 'Aliased<unknown>': sql, fieldAlias ts(2739)

Type 'PgColumn<{ name: "avg_price"; tableName: "avgPrice"; dataType: "number"; columnType: "PgReal"; data: number; driverParam: string | number; notNull: false; hasDefault: false; enumValues: undefined; baseColumn: never; }, {}, {}>' is missing the following properties from type 'Aliased<unknown>': sql, fieldAlias ts(2739)

2. Also, there is runtime error:

TypeError: Cannot read properties of undefined (reading 'queryChunks')
    at mapColumnsInSQLToAlias (node_modules/.pnpm/drizzle-orm@0.29.3_postgres@3.3.5/node_modules/src/alias.ts:111:24)
    at mapColumnsInAliasedSQLToAlias (node_modules/.pnpm/drizzle-orm@0.29.3_postgres@3.3.5/node_modules/src/alias.ts:107:25)
    at PgDialect.buildRelationalQueryWithoutPK (node_modules/.pnpm/drizzle-orm@0.29.3_postgres@3.3.5/node_modules/src/pg-core/dialect.ts:1143:14)
    at QueryPromise._getQuery (node_modules/.pnpm/drizzle-orm@0.29.3_postgres@3.3.5/node_modules/src/pg-core/query-builders/query.ts:109:23)
    at QueryPromise._toSQL (node_modules/.pnpm/drizzle-orm@0.29.3_postgres@3.3.5/node_modules/src/pg-core/query-builders/query.ts:126:22)
    at <anonymous> (node_modules/.pnpm/drizzle-orm@0.29.3_postgres@3.3.5/node_modules/src/pg-core/query-builders/query.ts:85:39)
    at Object.startActiveSpan (node_modules/.pnpm/drizzle-orm@0.29.3_postgres@3.3.5/node_modules/src/tracing.ts:27:11)
    at QueryPromise._prepare (node_modules/.pnpm/drizzle-orm@0.29.3_postgres@3.3.5/node_modules/src/pg-core/query-builders/query.ts:84:17)
    at <anonymous> (node_modules/.pnpm/drizzle-orm@0.29.3_postgres@3.3.5/node_modules/src/pg-core/query-builders/query.ts:139:16)
    at Object.startActiveSpan (node_modules/.pnpm/drizzle-orm@0.29.3_postgres@3.3.5/node_modules/src/tracing.ts:27:11)

TypeError: Cannot read properties of undefined (reading 'queryChunks')
    at mapColumnsInSQLToAlias (node_modules/.pnpm/drizzle-orm@0.29.3_postgres@3.3.5/node_modules/src/alias.ts:111:24)
    at mapColumnsInAliasedSQLToAlias (node_modules/.pnpm/drizzle-orm@0.29.3_postgres@3.3.5/node_modules/src/alias.ts:107:25)
    at PgDialect.buildRelationalQueryWithoutPK (node_modules/.pnpm/drizzle-orm@0.29.3_postgres@3.3.5/node_modules/src/pg-core/dialect.ts:1143:14)
    at QueryPromise._getQuery (node_modules/.pnpm/drizzle-orm@0.29.3_postgres@3.3.5/node_modules/src/pg-core/query-builders/query.ts:109:23)
    at QueryPromise._toSQL (node_modules/.pnpm/drizzle-orm@0.29.3_postgres@3.3.5/node_modules/src/pg-core/query-builders/query.ts:126:22)
    at <anonymous> (node_modules/.pnpm/drizzle-orm@0.29.3_postgres@3.3.5/node_modules/src/pg-core/query-builders/query.ts:85:39)
    at Object.startActiveSpan (node_modules/.pnpm/drizzle-orm@0.29.3_postgres@3.3.5/node_modules/src/tracing.ts:27:11)
    at QueryPromise._prepare (node_modules/.pnpm/drizzle-orm@0.29.3_postgres@3.3.5/node_modules/src/pg-core/query-builders/query.ts:84:17)
    at <anonymous> (node_modules/.pnpm/drizzle-orm@0.29.3_postgres@3.3.5/node_modules/src/pg-core/query-builders/query.ts:139:16)
    at Object.startActiveSpan (node_modules/.pnpm/drizzle-orm@0.29.3_postgres@3.3.5/node_modules/src/tracing.ts:27:11)

24 replies

DTDrizzle Team

•Created by irelynx on 9/10/2023 in #help

How to add custom SQL queries during or after migration in drizzle schema?

But I guess it is fine to create separate migration file manually for that kind of requirements 🙂 Thanks again!

9 replies

DTDrizzle Team

•Created by irelynx on 9/10/2023 in #help

How to add custom SQL queries during or after migration in drizzle schema?

for example, index with sorting one field in ascending order, and other in descending (aka create index if not exists "idx" on "tablename" ("field1" ASC, "field2" DESC)) or with where clause in it (aka create index if not exists "idx" on "tablename" ("field1" ASC, "field2" DESC) where "field3"='value')

9 replies

DTDrizzle Team

•Created by irelynx on 9/10/2023 in #help

How to add custom SQL queries during or after migration in drizzle schema?

Hi Raphaël, thanks for your response. Hmm, i think this solution can do the trick, but is there really no any other way to do it inside schema file?

9 replies

Gaming

Programming