Sillvva
Sillvva
PostsComments
DTDrizzle Team
Created by ArChak on 7/5/2024 in #help
How to check user input is a valid column before appending condition?
No description
4 replies
DTDrizzle Team
Created by ArChak on 7/5/2024 in #help
How to check user input is a valid column before appending condition?
Can you show the how you defined the columnFilters variable?
4 replies
DTDrizzle Team
Created by magicspon on 7/4/2024 in #help
How to query many to many? Are the docs out of date?
Alternatively 
db.query.events.findMany({
  with: {
    drinks: { // you could also change this key
      columns: {
        drinkId: true,
        eventId: true,
      },
      with: {
        drinks: true,
      },
    },
  },
});
db.query.events.findMany({
  with: {
    drinks: { // you could also change this key
      columns: {
        drinkId: true,
        eventId: true,
      },
      with: {
        drinks: true,
      },
    },
  },
});
7 replies
DTDrizzle Team
Created by magicspon on 7/4/2024 in #help
How to query many to many? Are the docs out of date?
You should change the relation key 
export const eventsRelations = relations(events, ({ many }) => ({
-    drinks: many(eventsToDrinks),
+    eventsToDrinks: many(eventsToDrinks),
}))

export const drinksRelations = relations(drinks, ({ many, one }) => ({
-    events: many(eventsToDrinks),
+    eventsToDrinks: many(eventsToDrinks),
}))
export const eventsRelations = relations(events, ({ many }) => ({
-    drinks: many(eventsToDrinks),
+    eventsToDrinks: many(eventsToDrinks),
}))

export const drinksRelations = relations(drinks, ({ many, one }) => ({
-    events: many(eventsToDrinks),
+    eventsToDrinks: many(eventsToDrinks),
}))
The key used in the rqb object should match the relation key 
db.query.events.findMany({
  with: {
    eventsToDrink: { // this key should match the eventsRelations key, which it does not
      columns: {
        drinkId: true,
        eventId: true,
      },
      with: {
        drinks: true, // this key should match the eventsToDrinkRelations key, which it already does
      },
    },
  },
});
db.query.events.findMany({
  with: {
    eventsToDrink: { // this key should match the eventsRelations key, which it does not
      columns: {
        drinkId: true,
        eventId: true,
      },
      with: {
        drinks: true, // this key should match the eventsToDrinkRelations key, which it already does
      },
    },
  },
});
7 replies
DTDrizzle Team
Created by Kenpaffu on 6/25/2024 in #help
insert multiple rows - onConflictDoUpdate
This guide provides helpful info on how to upsert multiple rows. https://orm.drizzle.team/learn/guides/upsert
2 replies
DTDrizzle Team
Created by Tobias on 6/25/2024 in #help
Insert returning and left join
returning is not a SELECT query. It's part of the INSERT query. In PostgreSQL you could do an INSERT inside a CTE, but not in SQLite. And even then, drizzle doesn't natively support that.
3 replies
DTDrizzle Team
Created by dp on 6/23/2024 in #help
Way to get relations via typeof someSchema.$inferSelect?
You can do it that way. You can also use this custom helper type. 
import * as schema from "$server/db/schema";
import {
    type BuildQueryResult,
    type DBQueryConfig,
    type ExtractTablesWithRelations
} from "drizzle-orm";

type TSchema = ExtractTablesWithRelations<typeof schema>;

export type QueryConfig<TableName extends keyof TSchema> = 
  DBQueryConfig<"one" | "many", boolean, TSchema, TSchema[TableName]>;

export type InferQueryModel<
  TableName extends keyof TSchema, 
  QBConfig extends QueryConfig<TableName> = {}
> = BuildQueryResult<
    TSchema,
    TSchema[TableName],
    QBConfig
>;
import * as schema from "$server/db/schema";
import {
    type BuildQueryResult,
    type DBQueryConfig,
    type ExtractTablesWithRelations
} from "drizzle-orm";

type TSchema = ExtractTablesWithRelations<typeof schema>;

export type QueryConfig<TableName extends keyof TSchema> = 
  DBQueryConfig<"one" | "many", boolean, TSchema, TSchema[TableName]>;

export type InferQueryModel<
  TableName extends keyof TSchema, 
  QBConfig extends QueryConfig<TableName> = {}
> = BuildQueryResult<
    TSchema,
    TSchema[TableName],
    QBConfig
>;
Then you use it like this. First parameter is base table name, second is the same query object drizzle users for relational queries. 
type Result = InferQueryModel<
  "logs",
  {
    columns: { id: true },
    with: {
      character: {
        columns: { id: true }
      }
    }
  }
>;

// type Result = { id: string; character: { id: string; } }
type Result = InferQueryModel<
  "logs",
  {
    columns: { id: true },
    with: {
      character: {
        columns: { id: true }
      }
    }
  }
>;

// type Result = { id: string; character: { id: string; } }
4 replies
DTDrizzle Team
Created by noahsolomon on 6/18/2024 in #help
Where clause dependent on with relation
You can use the exists filter and a subquery: https://orm.drizzle.team/docs/operators#exists Something like this: 
const news = await ctx.db.query.newsTable.findMany({
  limit: 20,
  offset: 20 * input.page,
  orderBy: (newsTable, { desc }) => [desc(newsTable.createdAt)],
  with: {
    tweets: {
      with: { pundit: true },
    },
  },
  where: (newsTable, { exists, eq }) => exists(
    db.select().from(tweetsTable).where(eq(tweetsTable.newsId, newsTable.id))
  )
});
const news = await ctx.db.query.newsTable.findMany({
  limit: 20,
  offset: 20 * input.page,
  orderBy: (newsTable, { desc }) => [desc(newsTable.createdAt)],
  with: {
    tweets: {
      with: { pundit: true },
    },
  },
  where: (newsTable, { exists, eq }) => exists(
    db.select().from(tweetsTable).where(eq(tweetsTable.newsId, newsTable.id))
  )
});
4 replies
DTDrizzle Team
Created by Einzi on 6/15/2024 in #help
Array of Objects
Also might be worth adding something like this on the schema definition for type inference .$type<{ foo: string }>()
6 replies
DTDrizzle Team
Created by Einzi on 6/15/2024 in #help
Array of Objects
It should
6 replies
DTDrizzle Team
Created by Einzi on 6/15/2024 in #help
Array of Objects
https://orm.drizzle.team/docs/column-types/pg#jsonb
6 replies
DTDrizzle Team
Created by Eddy Vinck on 6/13/2024 in #help
How to do an UPDATE with a JOIN in Drizzle ORM?
You don't need the .getSQL() on the subquery
5 replies
DTDrizzle Team
Created by xvx on 6/12/2024 in #help
How to dynamically set a row value using data from a JS object? (onConflictDoUpdate)
Yeah, I can't see any issue with that. I can't think of anything else off the top of my head, unfortunately.
13 replies
DTDrizzle Team
Created by xvx on 6/12/2024 in #help
How to dynamically set a row value using data from a JS object? (onConflictDoUpdate)
The values you're inserting also includes the financialAccountId?
13 replies
DTDrizzle Team
Created by xvx on 6/12/2024 in #help
How to dynamically set a row value using data from a JS object? (onConflictDoUpdate)
Hmm, it should work so long as the conflict target is unique by its definition/constraints.
13 replies
DTDrizzle Team
Created by xvx on 6/12/2024 in #help
How to dynamically set a row value using data from a JS object? (onConflictDoUpdate)
Do you have a unique constraint on organisationId and financialAccountId?
13 replies
DTDrizzle Team
Created by xvx on 6/12/2024 in #help
How to dynamically set a row value using data from a JS object? (onConflictDoUpdate)
https://orm.drizzle.team/learn/guides/upsert
13 replies
DTDrizzle Team
Created by Caspian Nightworth on 6/11/2024 in #help
Convert PostgreSQL to Drizzle ORM
This might work: https://orm.drizzle.team/docs/select#select-from-subquery 
const sqSources = db
  .select({
    spellId: sources.spellId,
    sourceText: sql<string>`string_agg(${sources.sourceText}, ', ')`.as('source_text')
  })
  .groupBy(sources.spellId)
  .as('sources');

const spellList = await db
  .select({
    spellId: spells.id,
    name: spells.name,
    description: spells.description,
    tooltip: spells.tooltip,
    patch: spells.patch,
    icon: spells.icon,
    type: spells.type,
    aspect: spells.aspect,
    whereToAquire: sqSources.sourceText,
  })
  .from(spells)
  .innerJoin(sqSources, eq(spells.id, sqSources.spellId))
  .orderBy(asc(spells.id));
const sqSources = db
  .select({
    spellId: sources.spellId,
    sourceText: sql<string>`string_agg(${sources.sourceText}, ', ')`.as('source_text')
  })
  .groupBy(sources.spellId)
  .as('sources');

const spellList = await db
  .select({
    spellId: spells.id,
    name: spells.name,
    description: spells.description,
    tooltip: spells.tooltip,
    patch: spells.patch,
    icon: spells.icon,
    type: spells.type,
    aspect: spells.aspect,
    whereToAquire: sqSources.sourceText,
  })
  .from(spells)
  .innerJoin(sqSources, eq(spells.id, sqSources.spellId))
  .orderBy(asc(spells.id));
And if you want all columns from spells, you can also do this: 
const spellList = await db
  .select({
    ...getTableColumns(spells),
    whereToAquire: sqSources.sourceText,
  })
  .from(spells)
  .innerJoin(sqSources, eq(spells.id, sqSources.spellId))
  .orderBy(asc(spells.id));
const spellList = await db
  .select({
    ...getTableColumns(spells),
    whereToAquire: sqSources.sourceText,
  })
  .from(spells)
  .innerJoin(sqSources, eq(spells.id, sqSources.spellId))
  .orderBy(asc(spells.id));
4 replies
DTDrizzle Team
Created by Headless on 6/10/2024 in #help
Typescript Error
That shows a type like: 
export type MySqlRawQueryResult = [ResultSetHeader, FieldPacket[]];
export type MySqlRawQueryResult = [ResultSetHeader, FieldPacket[]];
And you can keep following the type definitions
9 replies
DTDrizzle Team
Created by Headless on 6/10/2024 in #help
Typescript Error
No description
9 replies