DT
Drizzle Team•3w ago
T.

Referencing Column at runtime

This could be a Typescript question, but there may also be a better way to achieve my goal with Drizzle. 
const search = [
    {
      table: "users",
      column: "name",
      value: "%John%",
    },
    {
      table: "admins",
      column: "address",
      value: "%SmallTown%",
    },
  ];

  const conditions = new Set<SQL>();
  const dynamicQuery = db.select().from(usersTable).$dynamic();
  dynamicQuery.leftJoin(adminsTable, eq(adminsTable.userId, usersTable.id));

  for (const filter of search) {
    let table;
    switch (filter.table) {
      case "users":
        table = usersTable;
        break;
      case "admins":
        table = adminsTable;
        break;
      default:
        throw Error("Invalid table");
    }
    const propertyName = filter.column as keyof typeof table;
    const column = table[propertyName];

    if (column) {
      conditions.add(like(column, filter.value))
    }
  }
  dynamicQuery.where(and(...conditions));
  console.log(dynamicQuery.toSQL());
const search = [
    {
      table: "users",
      column: "name",
      value: "%John%",
    },
    {
      table: "admins",
      column: "address",
      value: "%SmallTown%",
    },
  ];

  const conditions = new Set<SQL>();
  const dynamicQuery = db.select().from(usersTable).$dynamic();
  dynamicQuery.leftJoin(adminsTable, eq(adminsTable.userId, usersTable.id));

  for (const filter of search) {
    let table;
    switch (filter.table) {
      case "users":
        table = usersTable;
        break;
      case "admins":
        table = adminsTable;
        break;
      default:
        throw Error("Invalid table");
    }
    const propertyName = filter.column as keyof typeof table;
    const column = table[propertyName];

    if (column) {
      conditions.add(like(column, filter.value))
    }
  }
  dynamicQuery.where(and(...conditions));
  console.log(dynamicQuery.toSQL());
The resulting query is fine, but I get the following type error on the variable column in conditions.add(like(column, filter.value)) see attached screenshot. I created a simple project on GitHub to illustrate the issue: https://github.com/tgourgon/dynamic-filter-drizzleorm-experiment The question is, in this scenario, how do I reference a column dynamically while keeping the TS compiler happy, Or is there a better way altogether?
GitHub
GitHub - tgourgon/dynamic-filter-drizzleorm-experiment
Contribute to tgourgon/dynamic-filter-drizzleorm-experiment development by creating an account on GitHub.
No description
1 Reply
Sillvva
Sillvva•3w ago
Here's how I'd do it: 
// If using RQBv2
type TRSchema = ExtractTablesWithRelations<typeof relations>;
// If using RQBv1
type TRSchema = ExtractTablesWithRelations<typeof schema>;

// The function ensures table and column names are string literal types and provides autocomplete
function createSearch<TableName extends keyof TRSchema, ColumnName extends keyof TRSchema[TableName]["columns"]>(table: TableName, column: ColumnName, value: string) {
    return {
        table,
        column,
        value
    }
}

const search = [
  createSearch("users", "name", "%John%"),
  createSearch("admins", "address", "%SmallTown%")  
];

const conditions = new Set<SQL>();
const dynamicQuery = db.select().from(usersTable).$dynamic();
dynamicQuery.leftJoin(adminsTable, eq(adminsTable.userId, usersTable.id));

for (const filter of search) {
  let table: TRSchema[typeof filter.table]["table"];
  switch (filter.table) {
    case "users":
      table = usersTable;
      break;
    case "admins":
      table = adminsTable;
      break;
    default:
      throw Error("Invalid table");
  }
  const column = table[filter.column];

  if (column) {
    conditions.add(like(column, filter.value))
  }
}
dynamicQuery.where(and(...conditions));
console.log(dynamicQuery.toSQL());
// If using RQBv2
type TRSchema = ExtractTablesWithRelations<typeof relations>;
// If using RQBv1
type TRSchema = ExtractTablesWithRelations<typeof schema>;

// The function ensures table and column names are string literal types and provides autocomplete
function createSearch<TableName extends keyof TRSchema, ColumnName extends keyof TRSchema[TableName]["columns"]>(table: TableName, column: ColumnName, value: string) {
    return {
        table,
        column,
        value
    }
}

const search = [
  createSearch("users", "name", "%John%"),
  createSearch("admins", "address", "%SmallTown%")  
];

const conditions = new Set<SQL>();
const dynamicQuery = db.select().from(usersTable).$dynamic();
dynamicQuery.leftJoin(adminsTable, eq(adminsTable.userId, usersTable.id));

for (const filter of search) {
  let table: TRSchema[typeof filter.table]["table"];
  switch (filter.table) {
    case "users":
      table = usersTable;
      break;
    case "admins":
      table = adminsTable;
      break;
    default:
      throw Error("Invalid table");
  }
  const column = table[filter.column];

  if (column) {
    conditions.add(like(column, filter.value))
  }
}
dynamicQuery.where(and(...conditions));
console.log(dynamicQuery.toSQL());

Did you find this page helpful?