DT
Drizzle Team•14mo ago
Marcel Overdijk

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.
0 Replies
No replies yetBe the first to reply to this messageJoin

Did you find this page helpful?