Drizzle Team•6mo ago

Inner joining and left joining the same table (aliased) causes the return type to be never

I'm getting a weird issue where if I make two table aliases for the same table and then inner join one while left joining the other, the return type of my query ends up being never[].

const fromCompany = aliasedTable(company, "fromCompany");
const toServiceArea = aliasedTable(serviceArea, "toServiceArea");
const toCompany = aliasedTable(company, "toCompany");

const data = await db
  .select({
    leadReferral,
    fromCompany,
    toServiceArea,
    toCompany,
  })
  .from(leadReferral)
  .innerJoin(fromCompany, eq(fromCompany.id, leadReferral.fromCompanyId))
  .leftJoin(toServiceArea, eq(toServiceArea.id, leadReferral.toServiceAreaId))
  .leftJoin(toCompany, eq(toCompany.id, toServiceArea.companyId));

const fromCompany = aliasedTable(company, "fromCompany");
const toServiceArea = aliasedTable(serviceArea, "toServiceArea");
const toCompany = aliasedTable(company, "toCompany");

const data = await db
  .select({
    leadReferral,
    fromCompany,
    toServiceArea,
    toCompany,
  })
  .from(leadReferral)
  .innerJoin(fromCompany, eq(fromCompany.id, leadReferral.fromCompanyId))
  .leftJoin(toServiceArea, eq(toServiceArea.id, leadReferral.toServiceAreaId))
  .leftJoin(toCompany, eq(toCompany.id, toServiceArea.companyId));

Unfortunately the type of data becomes:

const data: never[]

const data: never[]

I've reduced the Company table to the smallest it could possibly be but it makes absolutely no difference:

export const company = mysqlTable(
  "Company",
  {
    id: int("id").primaryKey().autoincrement(),
  }
);

export const company = mysqlTable(
  "Company",
  {
    id: int("id").primaryKey().autoincrement(),
  }
);

Interestingly, if both joins to the Company table are left joins or if both joins are inner joins, it works fine, but that's not my intended behavior.

// So this works
const data = await db
  .select({
    leadReferral,
    fromCompany,
    toServiceArea,
    toCompany,
  })
  .from(leadReferral)
  .leftJoin(fromCompany, eq(fromCompany.id, leadReferral.fromCompanyId))
  .leftJoin(toServiceArea, eq(toServiceArea.id, leadReferral.toServiceAreaId))
  .leftJoin(toCompany, eq(toCompany.id, toServiceArea.companyId));

// And so does this
const data = await db
  .select({
    leadReferral,
    fromCompany,
    toServiceArea,
    toCompany,
  })
  .from(leadReferral)
  .innerJoin(fromCompany, eq(fromCompany.id, leadReferral.fromCompanyId))
  .leftJoin(toServiceArea, eq(toServiceArea.id, leadReferral.toServiceAreaId))
  .innerJoin(toCompany, eq(toCompany.id, toServiceArea.companyId));

// So this works
const data = await db
  .select({
    leadReferral,
    fromCompany,
    toServiceArea,
    toCompany,
  })
  .from(leadReferral)
  .leftJoin(fromCompany, eq(fromCompany.id, leadReferral.fromCompanyId))
  .leftJoin(toServiceArea, eq(toServiceArea.id, leadReferral.toServiceAreaId))
  .leftJoin(toCompany, eq(toCompany.id, toServiceArea.companyId));

// And so does this
const data = await db
  .select({
    leadReferral,
    fromCompany,
    toServiceArea,
    toCompany,
  })
  .from(leadReferral)
  .innerJoin(fromCompany, eq(fromCompany.id, leadReferral.fromCompanyId))
  .leftJoin(toServiceArea, eq(toServiceArea.id, leadReferral.toServiceAreaId))
  .innerJoin(toCompany, eq(toCompany.id, toServiceArea.companyId));

What am I missing here? 🤔

13 Replies

PapaFinnOP•6mo ago

Another note is that, types aside, my query works as intended with one inner join and one left join.

Fabian B.•6mo ago

Having the exact same error! Did you find a solution for it? It seems that it does work in runtime, so the sql is generated correctly, but the types are never[]. I fixed it by just leftJoining both aliases.

PapaFinnOP•6mo ago

Unfortunately had to do the same.

Jannis•4mo ago

Mario564•4mo ago

@Ian Schoen Hi there, could you file an issue on Github providing the same details as you did above? Want to keep track of this as it seems like an important thing to fix.

PapaFinnOP•4mo ago

I had the same thought just a few days ago. Can do.

Mario564•4mo ago

Thanks! We'll look into solving this as soon as we can

DoggeSlapper•4mo ago

I'm not sure if could be related but I was doing some db.select({...geColumns(tableA), ...getColumns(tableB)})and result wasnt expected as table fields overlap solution was do an object

db.select({
tableA: geColumns(tableA), 
tableB: getColumns(tableB)
})

db.select({
tableA: geColumns(tableA), 
tableB: getColumns(tableB)
})

Mario564•4mo ago

This is intentional behavior due to how objects work in JS/TS, the keys from the 2nd object being spread will override any that are also present in the 1st

DoggeSlapper•4mo ago

ye ye, I know, but it wasnt so clear as examples had just 1 field override and I need to keep everything else as it was, tbh it would be usefull if there was a way to extend the select() behavour without instead of override it as you need to define every single column then

const data = await db
  .select({
    leadReferral,
    fromCompany,
    toServiceArea,
    toCompany,
  })

const data = await db
  .select({
    leadReferral,
    fromCompany,
    toServiceArea,
    toCompany,
  })

atlesat when I tried I couldnt pass an alias to select is that correct?

Mario564•4mo ago

you can fix the issue by doing this:

{
  ...geColumns(tableA),
  ...getColumns(tableB),
  problematicKey1: tableA.commonKey,
  problematicKey2: tableB.commonKey
}

{
  ...geColumns(tableA),
  ...getColumns(tableB),
  problematicKey1: tableA.commonKey,
  problematicKey2: tableB.commonKey
}

DoggeSlapper•4mo ago

what I did was group them like:

{
  tableA:...geColumns(tableA),
  tableB:...getColumns(tableB),
  someExtraCol: ......,
  someExtraCol2: .....
}

{
  tableA:...geColumns(tableA),
  tableB:...getColumns(tableB),
  someExtraCol: ......,
  someExtraCol2: .....
}

Idially I would like to keep select() behavour and just add the extra cols that what I mean but didnt found a quick way to have taht without table: getColumns(table) for each table involve in the query

PapaFinnOP•3mo ago

We just closed the related open issue: https://github.com/drizzle-team/drizzle-orm/issues/2989 The fix is simply to use alias instead of aliasedTable.

GitHub

[BUG]: Aliasing the same table twice and then inner joining one whi...

What version of drizzle-orm are you using? 0.31.1 What version of drizzle-kit are you using? No response Describe the Bug If I make two table aliases for the same table and then inner join one whil...

Gaming

Programming

Inner joining and left joining the same table (aliased) causes the return type to be never