selecting all the columns of table join

Hi everyone. is there a supported syntax to select all the columns of a table join without defining them?

await db
    .select({
      customerFileId: customer_file.id,
      referenceId: customer_file.reference_id,
      state: customer_file.state,
      backwardStates: customer_file.backward_states,
      forwardStates: customer_file.forward_states,
      cancelStates: customer_file.cancel_states,
      dateCreated: customer_file.date_created,
      // All comulns from user table
    })
    .from(customer_file)
    .innerJoin(user, eq(customer_file.owner_id, user.id))
    .where(and(eq(customer_file.id, customer_file_id), sql`${customer_file.date_created} > NOW() - INTERVAL '10 days'`))

await db
    .select({
      customerFileId: customer_file.id,
      referenceId: customer_file.reference_id,
      state: customer_file.state,
      backwardStates: customer_file.backward_states,
      forwardStates: customer_file.forward_states,
      cancelStates: customer_file.cancel_states,
      dateCreated: customer_file.date_created,
      // All comulns from user table
    })
    .from(customer_file)
    .innerJoin(user, eq(customer_file.owner_id, user.id))
    .where(and(eq(customer_file.id, customer_file_id), sql`${customer_file.date_created} > NOW() - INTERVAL '10 days'`))

in SQL I use something similar to this:

SELECT cf.id, cf.reference_id, cf.state, cf.backward_states, cf.forward_states, cf.cancel_states, cf.date_created, u.*
FROM iziflow.customer_file cf
JOIN iziflow.user u ON cf.owner_id = u.id

SELECT cf.id, cf.reference_id, cf.state, cf.backward_states, cf.forward_states, cf.cancel_states, cf.date_created, u.*
FROM iziflow.customer_file cf
JOIN iziflow.user u ON cf.owner_id = u.id

8 Replies

Mykhailo•12mo ago

Hello, @Dush. You can do something like

await db.select({
  // your fields that you want to select explicity
  
  // all columns from user table
  user: userTable // as I see, userTable is user in your case
})

await db.select({
  // your fields that you want to select explicity
  
  // all columns from user table
  user: userTable // as I see, userTable is user in your case
})

await db
    .select({
      customerFileId: customer_file.id,
      referenceId: customer_file.reference_id,
      state: customer_file.state,
      backwardStates: customer_file.backward_states,
      forwardStates: customer_file.forward_states,
      cancelStates: customer_file.cancel_states,
      dateCreated: customer_file.date_created,
      user: user
    })
    .from(customer_file)
    .innerJoin(user, eq(customer_file.owner_id, user.id))
    .where(and(eq(customer_file.id, customer_file_id), sql`${customer_file.date_created} > NOW() - INTERVAL '10 days'`))

await db
    .select({
      customerFileId: customer_file.id,
      referenceId: customer_file.reference_id,
      state: customer_file.state,
      backwardStates: customer_file.backward_states,
      forwardStates: customer_file.forward_states,
      cancelStates: customer_file.cancel_states,
      dateCreated: customer_file.date_created,
      user: user
    })
    .from(customer_file)
    .innerJoin(user, eq(customer_file.owner_id, user.id))
    .where(and(eq(customer_file.id, customer_file_id), sql`${customer_file.date_created} > NOW() - INTERVAL '10 days'`))

DushOP•12mo ago

I appreciate that!

francis•12mo ago

I'm not sure you can do user: userTable but there is a utility function you can use which is getTableColumns I use key: getTableColumns(myTable) a lot in my code

Mykhailo•12mo ago

@francis you solution is also good! user: userTable works as well btw

const result1 = db
  .select({
    vendorId: vendors.id,
    session: getTableColumns(sessions),
  })
  .from(vendors)
  .leftJoin(sessions, eq(vendors.id, sessions.vendorId))
  .toSQL();

const result2 = db
  .select({
    vendorId: vendors.id,
    session: sessions,
  })
  .from(vendors)
  .leftJoin(sessions, eq(vendors.id, sessions.vendorId))
  .toSQL();

console.log('result1', result1);
console.log('result2', result2);

const result1 = db
  .select({
    vendorId: vendors.id,
    session: getTableColumns(sessions),
  })
  .from(vendors)
  .leftJoin(sessions, eq(vendors.id, sessions.vendorId))
  .toSQL();

const result2 = db
  .select({
    vendorId: vendors.id,
    session: sessions,
  })
  .from(vendors)
  .leftJoin(sessions, eq(vendors.id, sessions.vendorId))
  .toSQL();

console.log('result1', result1);
console.log('result2', result2);

result1 {
  sql: 'select "vendors"."id", "sessions"."id", "sessions"."timestamp", "sessions"."vendor_id" from "vendors" left join "sessions" on "vendors"."id" = "sessions"."vendor_id"',
  params: []
}
result2 {
  sql: 'select "vendors"."id", "sessions"."id", "sessions"."timestamp", "sessions"."vendor_id" from "vendors" left join "sessions" on "vendors"."id" = "sessions"."vendor_id"',
  params: []
}

result1 {
  sql: 'select "vendors"."id", "sessions"."id", "sessions"."timestamp", "sessions"."vendor_id" from "vendors" left join "sessions" on "vendors"."id" = "sessions"."vendor_id"',
  params: []
}
result2 {
  sql: 'select "vendors"."id", "sessions"."id", "sessions"."timestamp", "sessions"."vendor_id" from "vendors" left join "sessions" on "vendors"."id" = "sessions"."vendor_id"',
  params: []
}

francis•12mo ago

oh interesting, I get typescript type errors when I do that

Mykhailo•12mo ago

if you want we can try to figure it out

francis•12mo ago

ah, jk, never mind. I figured out my problem. I am using getTableColumns at the top level, not nested in an object. in my case I have table A, that I am joining onto table B, but I don't actually care about any bits of table B in my result - I only am using it for a where filter so to get only table A I use .select(getTableColumns(tableA))

Mykhailo•12mo ago

nice

Gaming

Programming

selecting all the columns of table join