Drizzle Team•2mo ago

How to implement a generic/reusable function for multiple tables

I have two separate tables for article views and case views and currently have two backend endpoints to get the last viewed cases and articles:

const distinctCaseViewsSubquery = db
  .select({
    caseId: casesViews.caseId,
    createdAt: sql`MAX(${casesViews.createdAt})`.as("maxCreatedAt"),
  })
  .from(casesViews)
  .where(eq(casesViews.userId, userId))
  .groupBy(casesViews.caseId)
  .as("distinct_views");

const caseViews = await db
  .select({
    caseId: distinctCaseViewsSubquery.caseId,
  })
  .from(distinctCaseViewsSubquery)
  .orderBy(desc(distinctCaseViewsSubquery.createdAt))
  .limit(3);`

const distinctCaseViewsSubquery = db
  .select({
    caseId: casesViews.caseId,
    createdAt: sql`MAX(${casesViews.createdAt})`.as("maxCreatedAt"),
  })
  .from(casesViews)
  .where(eq(casesViews.userId, userId))
  .groupBy(casesViews.caseId)
  .as("distinct_views");

const caseViews = await db
  .select({
    caseId: distinctCaseViewsSubquery.caseId,
  })
  .from(distinctCaseViewsSubquery)
  .orderBy(desc(distinctCaseViewsSubquery.createdAt))
  .limit(3);`

The code is exactly the same for articlesViews (except it select 'articleId: articlesViews.articleId'), so the logic is duplicated for both functions. Instead I want to create a function which takes the table as a (generic) argument along with a column (in my case 'articleId' or 'caseId'). Can someone help me with this? I already tried it like shown below but i could't figure out the type for 'idColumn' and of course got an error for 'table[idColumn]'.

async function getLastViewedItems<Table extends (typeof articlesViews | typeof casesViews)>(table: Table, idColumn: any, userId: string)
{
  const distinctViewsSubquery = db
    .select({
      createdAt: sql`MAX(${table.createdAt})`.as("maxCreatedAt"),
      itemId: table[idColumn],
    })
    .from(table)
    .where(eq(table.userId, userId))
    .groupBy(table[idColumn])
    .as("distinct_views");

  const views = await db
    .select({
      itemId: distinctViewsSubquery.itemId,
    })
    .from(distinctViewsSubquery)
    .orderBy(desc(distinctViewsSubquery.createdAt))
    .limit(3);

  return views;
}

async function getLastViewedItems<Table extends (typeof articlesViews | typeof casesViews)>(table: Table, idColumn: any, userId: string)
{
  const distinctViewsSubquery = db
    .select({
      createdAt: sql`MAX(${table.createdAt})`.as("maxCreatedAt"),
      itemId: table[idColumn],
    })
    .from(table)
    .where(eq(table.userId, userId))
    .groupBy(table[idColumn])
    .as("distinct_views");

  const views = await db
    .select({
      itemId: distinctViewsSubquery.itemId,
    })
    .from(distinctViewsSubquery)
    .orderBy(desc(distinctViewsSubquery.createdAt))
    .limit(3);

  return views;
}

Thanks in advance 🙏🏼

1 Reply

DoggeSlapper•2mo ago

one option would be pass the select as whole

Gaming

Programming

How to implement a generic/reusable function for multiple tables