Drizzle Team•2mo 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? 🤔

3 Replies

PapaFinn•2mo ago

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

Fabian B.•2mo 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.

PapaFinn•2mo ago

Unfortunately had to do the same.

Gaming

Programming

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