Marcel Overdijk Posts - Answer Overflow

Marcel Overdijk

Explore posts from servers

CDCloudflare Developers KKysely PPrisma HHono DTDrizzle Team

•Created by Marcel Overdijk on 9/9/2024 in #help

creating a join for re-use

Drizzle support creating re-usable where and order by expressions like: gt(customerTable.salary, 5000); desc(customerTable.salary) for this no query builder (qb ) is needed. is something similar available (without the need of a qb) to create joins (and apply them later)? and then later do something like:

const join = // a set of joins to add
const where = // a set of conditions to add
const orderBy = // a set of order by's to add
const pagination = // pagination params

const result = await db
  .select()
  .from(customerTable)
  .join(...joins)
  .where(...where)
  .orderBy(...orderBy)
  .limit(pagination.limit)
  .offset(pagination.offset)

const join = // a set of joins to add
const where = // a set of conditions to add
const orderBy = // a set of order by's to add
const pagination = // pagination params

const result = await db
  .select()
  .from(customerTable)
  .join(...joins)
  .where(...where)
  .orderBy(...orderBy)
  .limit(pagination.limit)
  .offset(pagination.offset)

for where and orderBy this is already possible, but for joins I could't find anything. my use case is that I'm doing some dynamic stuff. for now I created my own wrapper like:

 
export interface Join {
  type: 'inner' | 'left';
  table: Table;
  on: SQL | SQL[];
}

 
export interface Join {
  type: 'inner' | 'left';
  table: Table;
  on: SQL | SQL[];
}

and I'm doing something like:

 
if (joins) {
  joins.forEach((join) => {
    if (!appliedJoins.includes(join)) {
      const conditions = Array.isArray(join.on) ? join.on : [join.on];
      if (join.type === 'left') {
        qb.leftJoin(join.table, and(...conditions));
      }
      if (join.type === 'inner') {
        qb.innerJoin(join.table, and(...conditions));
      }
      appliedJoins.push(join);
    }
  });
}

 
if (joins) {
  joins.forEach((join) => {
    if (!appliedJoins.includes(join)) {
      const conditions = Array.isArray(join.on) ? join.on : [join.on];
      if (join.type === 'left') {
        qb.leftJoin(join.table, and(...conditions));
      }
      if (join.type === 'inner') {
        qb.innerJoin(join.table, and(...conditions));
      }
      appliedJoins.push(join);
    }
  });
}

Note what I'm doing is quite dyamic, so having a re-usable withCountryJoin(qb) won't help me unfortunately.

1 replies

DTDrizzle Team

•Created by Marcel Overdijk on 9/1/2024 in #help

Using `$dynamic()` to enhance where clause

I have a function like: export const applyFilter = (qb: SQLiteSelect, paging: Paging, filter: string | undefined) => { .. } which adds limit and offset and also where using qb.where(..). This works great, but now I have the use case the qb received already contains a where clause. But the qb.where(..) overwrites it unfortunately. Is there a way to merge them instead, if the qb already has a where?

22 replies

DTDrizzle Team

•Created by Marcel Overdijk on 8/26/2024 in #help

[sqlite] How to order by rowid?

Having a query like:

db.query.countryTable.findAll({
    where: eq(countryTable.continentId, continentId),
    orderBy: // how to order by rowid  
})

db.query.countryTable.findAll({
    where: eq(countryTable.continentId, continentId),
    orderBy: // how to order by rowid  
})

I would like to order by the sqlite rowid. Maybe in this example it would make more sense to order by the country name, but this just for an example. In my case I have no natural column to order by, but want to sort on rowid.

4 replies

DTDrizzle Team

•Created by Marcel Overdijk on 7/31/2024 in #help

Dynamically adding joins based on relation?

I have this setup:

export const circuitTable = sqliteTable('circuit', {
  id: text('id').notNull().primaryKey(),
  name: text('name').notNull(),
  placeName: text('place_name').notNull(),
  countryId: text('country_id')
    .notNull()
    .references(() => countryTable.id),
});

export const circuitRelations = relations(circuitTable, ({ one }) => ({
  country: one(countryTable, {
    fields: [circuitTable.countryId],
    references: [countryTable.id],
  }),
}));

export const circuitTable = sqliteTable('circuit', {
  id: text('id').notNull().primaryKey(),
  name: text('name').notNull(),
  placeName: text('place_name').notNull(),
  countryId: text('country_id')
    .notNull()
    .references(() => countryTable.id),
});

export const circuitRelations = relations(circuitTable, ({ one }) => ({
  country: one(countryTable, {
    fields: [circuitTable.countryId],
    references: [countryTable.id],
  }),
}));

I can easily refer to columns within the circuit table like circuitTable.name or circuitTable.placeName. Can I also refer to relations to dynamically add a join? E.g.:

const baseQuery = ctx.mydb
  .select({
    ...getTableColumns(circuitTable),
  })
  .from(circuitTable)
  .$dynamic();

const baseQuery = ctx.mydb
  .select({
    ...getTableColumns(circuitTable),
  })
  .from(circuitTable)
  .$dynamic();

and then based on user input I want to add some joins dynamically. E.g. the user input could be sort on placeName or country.name, and in the latter case I want to add the country join. As I have this in many places I would like to do something smart like:

const sortFields = {
  'id': circuitTable.id,
  'name': circuitTable.name,
  'placeName': circuitTable.placeName,
  'countryId': circuitTable.countryId,
  'countryName': {
    column: countryTable.name,
    join: circuitRelations.country, // not possible
  }
};

const sortFields = {
  'id': circuitTable.id,
  'name': circuitTable.name,
  'placeName': circuitTable.placeName,
  'countryId': circuitTable.countryId,
  'countryName': {
    column: countryTable.name,
    join: circuitRelations.country, // not possible
  }
};

based on such configs I wanted to apply the sort based on user input sort field. Would something like that be possible?

7 replies

DTDrizzle Team

•Created by Marcel Overdijk on 7/8/2024 in #help

Typesafe where? argument possible?

I have a service class containing:

  async findAll(limit: number, offset: number): Promise<Circuit[]> {
    logger.debug('CircuitService.findAll() called with limit=%s, offset=%s', limit, offset);

    // Retrieve the circuits from the database.
    const circuits = await this.f1db.query.circuits.findMany({ limit, offset });

    return circuits;
  }

  async findAll(limit: number, offset: number): Promise<Circuit[]> {
    logger.debug('CircuitService.findAll() called with limit=%s, offset=%s', limit, offset);

    // Retrieve the circuits from the database.
    const circuits = await this.f1db.query.circuits.findMany({ limit, offset });

    return circuits;
  }

now I want to add a where? argument to this findAll function. Is there a type I can use from Drizzle to make this where? arg it typesafe?

8 replies

Gaming

Programming