titongo Comments - Answer Overflow

titongo

Explore posts from servers

RRailway DTDrizzle Team

Posts Comments

DTDrizzle Team

•Created by francis on 8/8/2024 in #help

What's the recommended way to do a basic filter by a boolean column's value?

Is it not possible to use .where(eq(appointment.canceled, true))

3 replies

DTDrizzle Team

•Created by titongo on 8/3/2024 in #help

limiting a join when filtering by an extra field.

basically, turning this from the drizzle docs into a relational query: https://orm.drizzle.team/docs/select#select-from-subquery Select from subquery Just like in SQL, you can embed queries into other queries by using the subquery API:

const sq = db.select().from(users).where(eq(users.id, 42)).as('sq');
const result = await db.select().from(sq);
select "id", "name", "age" from (select "id", "name", "age" from "users" where "id" = 42) "sq";

const sq = db.select().from(users).where(eq(users.id, 42)).as('sq');
const result = await db.select().from(sq);
select "id", "name", "age" from (select "id", "name", "age" from "users" where "id" = 42) "sq";

Subqueries can be used in any place where a table can be used, for example in joins:

const sq = db.select().from(users).where(eq(users.id, 42)).as('sq');
const result = await db.select().from(users).leftJoin(sq, eq(users.id, sq.id));
select "users"."id", "users"."name", "users"."age", "sq"."id", "sq"."name", "sq"."age" from "users"
  left join (select "id", "name", "age" from "users" where "id" = 42) "sq"
    on "users"."id" = "sq"."id";

const sq = db.select().from(users).where(eq(users.id, 42)).as('sq');
const result = await db.select().from(users).leftJoin(sq, eq(users.id, sq.id));
select "users"."id", "users"."name", "users"."age", "sq"."id", "sq"."name", "sq"."age" from "users"
  left join (select "id", "name", "age" from "users" where "id" = 42) "sq"
    on "users"."id" = "sq"."id";

2 replies

DTDrizzle Team

•Created by titongo on 6/15/2024 in #help

nesting db calls in transactions without using the callback argument

Thanks a lot!

6 replies

DTDrizzle Team

•Created by titongo on 6/15/2024 in #help

nesting db calls in transactions without using the callback argument

🤔

6 replies

DTDrizzle Team

•Created by titongo on 6/15/2024 in #help

nesting db calls in transactions without using the callback argument

bump I guess?

6 replies

DTDrizzle Team

•Created by Hello, I’m Allie! on 5/11/2024 in #help

Environment-based Connection Configs?

oh okay thanks

5 replies

DTDrizzle Team

•Created by Hello, I’m Allie! on 5/11/2024 in #help

Environment-based Connection Configs?

Hello there, were you able to do this? I'm finding this section of the docs confusing but IIUC this is it, right? https://orm.drizzle.team/docs/get-started-sqlite#cloudflare-d1

5 replies

DTDrizzle Team

•Created by Aguilar on 5/19/2024 in #help

drizzle config is weird

This may help https://orm.drizzle.team/kit-docs/config-reference#driver

5 replies

DTDrizzle Team

•Created by titongo on 12/14/2023 in #help

Extremely high usage due to weird ordering used by drizzle

11 replies

DTDrizzle Team

•Created by titongo on 12/14/2023 in #help

Extremely high usage due to weird ordering used by drizzle

11 replies

DTDrizzle Team

•Created by titongo on 12/14/2023 in #help

Extremely high usage due to weird ordering used by drizzle

If anyone is facing something similar, here is my (extremely ugly) workaround:

const problemString = `from (select *, row_number() over (order by \`debatesTable_posts\`.\`created_at\` desc) from \`posts\` \`debatesTable_posts\` where \`debatesTable_posts\`.\`debate_id\` = \`debatesTable\`.\`id\` limit 16) \`debatesTable_posts\`), json_array()) as \`posts\``;
const replacedWith = `FROM (SELECT * FROM \`posts\` \`debatesTable_posts\` WHERE \`debatesTable_posts\`.\`debate_id\` = \`debatesTable\`.\`id\` ORDER BY \`debatesTable_posts\`.\`created_at\` DESC LIMIT 16) \`debatesTable_posts\`), json_array()) AS \`posts\``;

const connection = connect({
  host: config.dbHost,
  username: config.dbUsername,
  password: config.dbPassword,
  fetch: (url, options) => {
    let modifiedBody = options?.body || "";
    if (!modifiedBody || modifiedBody.indexOf("row_number()") !== -1) {
      modifiedBody = modifiedBody.replace(problemString, replacedWith);
    }
    return fetch(url, { ...options, body: modifiedBody });
  },
});

const problemString = `from (select *, row_number() over (order by \`debatesTable_posts\`.\`created_at\` desc) from \`posts\` \`debatesTable_posts\` where \`debatesTable_posts\`.\`debate_id\` = \`debatesTable\`.\`id\` limit 16) \`debatesTable_posts\`), json_array()) as \`posts\``;
const replacedWith = `FROM (SELECT * FROM \`posts\` \`debatesTable_posts\` WHERE \`debatesTable_posts\`.\`debate_id\` = \`debatesTable\`.\`id\` ORDER BY \`debatesTable_posts\`.\`created_at\` DESC LIMIT 16) \`debatesTable_posts\`), json_array()) AS \`posts\``;

const connection = connect({
  host: config.dbHost,
  username: config.dbUsername,
  password: config.dbPassword,
  fetch: (url, options) => {
    let modifiedBody = options?.body || "";
    if (!modifiedBody || modifiedBody.indexOf("row_number()") !== -1) {
      modifiedBody = modifiedBody.replace(problemString, replacedWith);
    }
    return fetch(url, { ...options, body: modifiedBody });
  },
});

11 replies

DTDrizzle Team

•Created by titongo on 12/14/2023 in #help

Extremely high usage due to weird ordering used by drizzle

Sure!

11 replies

DTDrizzle Team

•Created by titongo on 12/14/2023 in #help

Extremely high usage due to weird ordering used by drizzle

I tried replacing it with

FROM
  (SELECT *
   FROM `posts` `debatesTable_posts`
   WHERE `debatesTable_posts`.`debate_id` = `debatesTable`.`id`
   LIMIT 16 ORDER BY `debatesTable_posts`.`created_at` DESC) `debatesTable_posts`), json_array()) AS `posts`

FROM
  (SELECT *
   FROM `posts` `debatesTable_posts`
   WHERE `debatesTable_posts`.`debate_id` = `debatesTable`.`id`
   LIMIT 16 ORDER BY `debatesTable_posts`.`created_at` DESC) `debatesTable_posts`), json_array()) AS `posts`

and row reads went from 20.000 to 37.

11 replies

DTDrizzle Team

•Created by titongo on 12/14/2023 in #help

Extremely high usage due to weird ordering used by drizzle

for some reason, the whole row_number() thing is slowing things down, and what is causing it is orderBy: desc(postsTable.createdAt)

11 replies

DTDrizzle Team

•Created by titongo on 12/14/2023 in #help

Extremely high usage due to weird ordering used by drizzle

particularly, this portion of the query seems to be problematic

FROM
  (SELECT *, row_number() OVER (
      ORDER BY `debatesTable_posts`.`created_at` DESC)
      FROM `posts` `debatesTable_posts`
      WHERE `debatesTable_posts`.`debate_id` = `debatesTable`.`id`
      LIMIT 16) `debatesTable_posts`), json_array()) AS `posts`

FROM
  (SELECT *, row_number() OVER (
      ORDER BY `debatesTable_posts`.`created_at` DESC)
      FROM `posts` `debatesTable_posts`
      WHERE `debatesTable_posts`.`debate_id` = `debatesTable`.`id`
      LIMIT 16) `debatesTable_posts`), json_array()) AS `posts`