irelynx
irelynx
PostsComments
DTDrizzle Team
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/[email protected][email protected]/node_modules/src/alias.ts:111:24)
    at mapColumnsInAliasedSQLToAlias (node_modules/.pnpm/[email protected][email protected]/node_modules/src/alias.ts:107:25)
    at PgDialect.buildRelationalQueryWithoutPK (node_modules/.pnpm/[email protected][email protected]/node_modules/src/pg-core/dialect.ts:1143:14)
    at QueryPromise._getQuery (node_modules/.pnpm/[email protected][email protected]/node_modules/src/pg-core/query-builders/query.ts:109:23)
    at QueryPromise._toSQL (node_modules/.pnpm/[email protected][email protected]/node_modules/src/pg-core/query-builders/query.ts:126:22)
    at <anonymous> (node_modules/.pnpm/[email protected][email protected]/node_modules/src/pg-core/query-builders/query.ts:85:39)
    at Object.startActiveSpan (node_modules/.pnpm/[email protected][email protected]/node_modules/src/tracing.ts:27:11)
    at QueryPromise._prepare (node_modules/.pnpm/[email protected][email protected]/node_modules/src/pg-core/query-builders/query.ts:84:17)
    at <anonymous> (node_modules/.pnpm/[email protected][email protected]/node_modules/src/pg-core/query-builders/query.ts:139:16)
    at Object.startActiveSpan (node_modules/.pnpm/[email protected][email protected]/node_modules/src/tracing.ts:27:11)
TypeError: Cannot read properties of undefined (reading 'queryChunks')
    at mapColumnsInSQLToAlias (node_modules/.pnpm/[email protected][email protected]/node_modules/src/alias.ts:111:24)
    at mapColumnsInAliasedSQLToAlias (node_modules/.pnpm/[email protected][email protected]/node_modules/src/alias.ts:107:25)
    at PgDialect.buildRelationalQueryWithoutPK (node_modules/.pnpm/[email protected][email protected]/node_modules/src/pg-core/dialect.ts:1143:14)
    at QueryPromise._getQuery (node_modules/.pnpm/[email protected][email protected]/node_modules/src/pg-core/query-builders/query.ts:109:23)
    at QueryPromise._toSQL (node_modules/.pnpm/[email protected][email protected]/node_modules/src/pg-core/query-builders/query.ts:126:22)
    at <anonymous> (node_modules/.pnpm/[email protected][email protected]/node_modules/src/pg-core/query-builders/query.ts:85:39)
    at Object.startActiveSpan (node_modules/.pnpm/[email protected][email protected]/node_modules/src/tracing.ts:27:11)
    at QueryPromise._prepare (node_modules/.pnpm/[email protected][email protected]/node_modules/src/pg-core/query-builders/query.ts:84:17)
    at <anonymous> (node_modules/.pnpm/[email protected][email protected]/node_modules/src/pg-core/query-builders/query.ts:139:16)
    at Object.startActiveSpan (node_modules/.pnpm/[email protected][email protected]/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