Drizzle TeamDT
Drizzle Team•2y ago
PapaFinn

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));


Unfortunately the type of data becomes:
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(),
  }
);


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));


What am I missing here? 🤔
Was this page helpful?