PapaFinn Posts - Answer Overflow

PapaFinn

•Created by PapaFinn on 7/10/2024 in #help

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? 🤔

16 replies

DTDrizzle Team

•Created by PapaFinn on 6/20/2023 in #help

Extracting literal values from Drizzle enums

I'm trying to make a zod enum schema from the values of a Drizzle enum I've created. I've looked through several other posts trying to do the same thing and it's unclear to me how to do this, particularly for mysqlEnum. The most promising way came from inspecting the enums at runtime. I found that I could access the values, just as I wanted, at myEnum.config.enumValues. The only problem is that Typescript declares config protected and I get type errors.

7 replies

DTDrizzle Team

•Created by PapaFinn on 4/27/2023 in #help

Does Drizzle support MySQL's LAST_INSERT_ID function?

I'm wanting to return a newly created record in the db immediately after a POST. MySQL doesn't support returning so it seems the alternative is a separate request in the same transaction that fetches the new record. It appears that the easiest way to do this is with MySQL's LAST_INSERT_ID function. Does Drizzle support this or does this have to be done with raw SQL?

12 replies

Gaming

Programming