Drizzle Team•3w ago

How to dynamically select all columns from one table, and join a single column from another table?

Its pretty often that I need to grab some details from a relational table and add it in to my original query. However, when I do this, I then need to explicitly call out all columns on the original table, and compose in my values from the join table. This can become tedious when I have many columns or when a column is added on the original table. I was hoping I might be able to spread out my reference to the PgTable and just add in one other key for the other table, but this isn't a valid query. Wrong

    const existingReport = await db
      .select(
        {
          ...reports,
          templateName: fileUpload.originalFileName,
        }
      )
      .from(reports)
      .orderBy(desc(reports.lastUpdated))
      .limit(1)
      .leftJoin(fileUpload, eq(reports.linkedFileID, fileUpload.id));

    const existingReport = await db
      .select(
        {
          ...reports,
          templateName: fileUpload.originalFileName,
        }
      )
      .from(reports)
      .orderBy(desc(reports.lastUpdated))
      .limit(1)
      .leftJoin(fileUpload, eq(reports.linkedFileID, fileUpload.id));

"Correct"

const existingDetails = await db
    .select({
      id: reports.id,
      name: reports.name,
      linkedFileID: reports.linkedFileID,
      effectiveDate: reports.effectiveDate,
      uploadedBy: reports.uploadedBy,
      lastUpdated: reports.lastUpdated,
      createdOn: reports.createdOn,
      nextRelease: reports.nextRelease,
      templateName: fileUpload.originalFileName,
    })
    .from(reportDetails)
    .where(eq(reportDetails.name, reportName))
    .leftJoin(fileUpload, eq(reportDetails.template, fileUpload.id));

const existingDetails = await db
    .select({
      id: reports.id,
      name: reports.name,
      linkedFileID: reports.linkedFileID,
      effectiveDate: reports.effectiveDate,
      uploadedBy: reports.uploadedBy,
      lastUpdated: reports.lastUpdated,
      createdOn: reports.createdOn,
      nextRelease: reports.nextRelease,
      templateName: fileUpload.originalFileName,
    })
    .from(reportDetails)
    .where(eq(reportDetails.name, reportName))
    .leftJoin(fileUpload, eq(reportDetails.template, fileUpload.id));

I am almost certain I am missing something here to be able to generate my select from the table schema itself. Does anyone know what the proper methods are to achieve this?

2 Replies

F1x•3w ago

just specify the table don't spread it so reports: reportsTable or whatever u call it

NotZeldaOP•2w ago

This would only put it in a key labelled the same way as the table. My hope is to be able to return a new combject with some properties combines instead of nested. This can be achieved by labelling each key manually, but no automatic way.

Gaming

Programming

How to dynamically select all columns from one table, and join a single column from another table?