DT
Drizzle Team•2mo ago
NotZelda

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
F1x•2mo ago
just specify the table don't spread it so reports: reportsTable or whatever u call it
NotZelda
NotZeldaOP•2mo 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.

Did you find this page helpful?