Drizzle Team•3d ago

Is this a bug or I am just an idiot

So basically I have 3 tables named as chapter, volume and novel. I was trying to get all the novels (which is 8 right now), and latest 5 chapters of these novels. To achieve this I was trying to use subquery of volume and chapter combined and then giving them a row number. Because volume and chapter both have a column name as number somehow it is causing issue 😕 . For reference: I'm using supabase with @vercel/postgres My error:

error: column reference "number" is ambiguous
    at F:\projects\quaslation\node_modules\@neondatabase\serverless\index.js:1345:74
    at process.processTicksAndRejections (node:internal/process/task_queues:95:5)
    at VercelPgPreparedQuery.execute (F:\projects\quaslation\node_modules\src\vercel-postgres\session.ts:60:20)
    at main (f:\projects\quaslation\temp.ts:35:18) {
  length: 111,
  severity: 'ERROR',
  code: '42702',
  detail: undefined,
  hint: undefined,
  position: '62',
  internalPosition: undefined,
  internalQuery: undefined,
  where: undefined,
  schema: undefined,
  table: undefined,
  column: undefined,
  dataType: undefined,
  constraint: undefined,
  file: 'parse_relation.c',
  line: '816',
  routine: 'scanRTEForColumn'
}

error: column reference "number" is ambiguous
    at F:\projects\quaslation\node_modules\@neondatabase\serverless\index.js:1345:74
    at process.processTicksAndRejections (node:internal/process/task_queues:95:5)
    at VercelPgPreparedQuery.execute (F:\projects\quaslation\node_modules\src\vercel-postgres\session.ts:60:20)
    at main (f:\projects\quaslation\temp.ts:35:18) {
  length: 111,
  severity: 'ERROR',
  code: '42702',
  detail: undefined,
  hint: undefined,
  position: '62',
  internalPosition: undefined,
  internalQuery: undefined,
  where: undefined,
  schema: undefined,
  table: undefined,
  column: undefined,
  dataType: undefined,
  constraint: undefined,
  file: 'parse_relation.c',
  line: '816',
  routine: 'scanRTEForColumn'
}

Solution:

@Shashank Hi, I'm pretty sure the issue is to do with the chapter number and volume number, and the fix should be as simple as this:

chapterNumber: capterTable.number.getSQL().as('chapter_number'),
volumeNumber: volumeTable.number.getSQL().as('volume_number')

chapterNumber: capterTable.number.getSQL().as('chapter_number'),
volumeNumber: volumeTable.number.getSQL().as('volume_number')

...

Jump to solution

5 Replies

Solution

Mario564•3d ago

@Shashank Hi, I'm pretty sure the issue is to do with the chapter number and volume number, and the fix should be as simple as this:

chapterNumber: capterTable.number.getSQL().as('chapter_number'),
volumeNumber: volumeTable.number.getSQL().as('volume_number')

chapterNumber: capterTable.number.getSQL().as('chapter_number'),
volumeNumber: volumeTable.number.getSQL().as('volume_number')

Mario564•3d ago

This should resolve the ambiguity the error is describing

Shashank•3d ago

Yupp it does but the problem is it also takes away the power of typescript for chapterNumber and volumeNumber I guess this is the workaround for now

Mario564•3d ago

Oh yeah, that can be an issue. Adding to the already kinda verbose solution, you can also add .mapWith(Number) to TS knows it's a number (add it before .as()) We'll look into aliasing columns without this clunky behavior sometime soon

Shashank•3d ago

yeah that will be gr8 for now I'm planning to use relational queries. It fits better with my use case.

Gaming

Programming

Is this a bug or I am just an idiot