NotZelda Posts - Answer Overflow

NotZelda

•Created by NotZelda on 12/3/2024 in #help

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?

3 replies

Gaming

Programming