Join

Join are available in both fetchOne and fetchAll.

Available join methods are:

• INNER JOIN
• LEFT JOIN
• CROSS JOIN

Note that SQLite doesn't support RIGHT JOIN neither FULL JOIN.

Implicit Inner Join

const qb = new D1QB(env.DB)

const fetched = await qb.fetchAll({
  tableName: 'employees',
  fields: ['role', 'department', 'payroll.salary'],
  where: {
    conditions: 'department = ?1',
    params: ['HR'],
  },
  join: {
    table: 'payroll',
    on: 'payroll.employee_id = employees.id',
  },
})

Explicit Inner Join

const qb = new D1QB(env.DB)

const fetched = await qb.fetchAll({
  tableName: 'employees',
  fields: ['role', 'department', 'payroll.salary'],
  where: {
    conditions: 'department = ?1',
    params: ['HR'],
  },
  join: {
    type: JoinTypes.INNER,
    table: 'payroll',
    on: 'payroll.employee_id = employees.id',
  },
})

Left Join

const qb = new D1QB(env.DB)

const fetched = await qb.fetchAll({
  tableName: 'employees',
  fields: '*',
  join: {
    type: JoinTypes.LEFT,
    table: 'payroll',
    on: 'payroll.employee_id = employees.id',
  },
})

Multiple Joins

const qb = new D1QB(env.DB)

const fetched = await qb.fetchAll({
  tableName: 'employees',
  fields: '*',
  join: [
    {
      table: 'payroll',
      on: 'payroll.employee_id = employees.id',
    },
    {
      table: 'offices',
      on: 'testTable.office_id = offices.id',
    },
  ],
})