Kysely•4w ago

Identically named columns in multiple tables - How to use innerJoin.selectAll()?

Hey, I am new to using kysely, and also relatively new to using PostgreSQL. I have trouble with the following query:

const res = await db.selectFrom('tables')
  .where('tables.organizationId', '=', params.orgId)
  .where('tables.id', '=', params.tableId)
  .innerJoin('tableLeadStates', 'tableLeadStates.tableId', 'tables.id')
  .selectAll()
  .executeTakeFirstOrThrow();

const res = await db.selectFrom('tables')
  .where('tables.organizationId', '=', params.orgId)
  .where('tables.id', '=', params.tableId)
  .innerJoin('tableLeadStates', 'tableLeadStates.tableId', 'tables.id')
  .selectAll()
  .executeTakeFirstOrThrow();

Because both tables and tableLeadStates have identically named name and id as well as color columns, this causes overlapping keys in my response. Intellisense does not seem to suggest that kysely is prefixing the tables. What am I doing wrong here and how do I solve this? I know that this is basically just how SQL works, but I am still wondering, how do I alias my selectAll() or add a prefix another way?

Solution:

Hey 👋🏻 You have 3 options: 1. use table scoped selectAll on 1 table (selectAll('tables')) and then select the other table/s columns while aliasing columns that exist multiple "tables"....

Jump to solution

1 Reply

Solution

Igal•3w ago

Hey 👋🏻 You have 3 options: 1. use table scoped selectAll on 1 table (selectAll('tables')) and then select the other table/s columns while aliasing columns that exist multiple "tables". 2. using select entirely and aliasing columns that exist in multiple tables. 3. nesting. e.g. via jsonArrayFrom. (https://kysely.dev/docs/recipes/relations)

Gaming

Programming

Identically named columns in multiple tables - How to use innerJoin.selectAll()?