[BUG]: select with optional left join gives IsDrizzleTable type

[autosync-1]

What version of drizzle-orm are you using?

0.28.6

What version of drizzle-kit are you using?

0.19.3

Describe the Bug

Code

  async findAll(input: z.infer<typeof supplierFindAllSchema>) {
    const { _, $inferSelect, $inferInsert, ...columns } = suppliers;

    const query = this.db
      .select({
        ...columns,
        ...(input.withCount && {
          productsCount: sql<number>`count(${products.id})`
            .mapWith(Number)
            .as('products_count'),
        }),
      })
      .from(suppliers);
    if (input.haveProducts || input.withCount) {
      query.leftJoin(products, eq(suppliers, products.supplierId));
    }
    if (input.haveProducts) {
      query.having(sql`products_count >= 1`);
    }
    const result = await query.execute();
    return result;
  }

this code giving me this error

typescript: Return type of public method from exported class has or is using name 'IsDrizzleTable' from external module "C:/Users/hitech/code/autosync_nest/node_modules/.pnpm/drizzle-orm@0.28.6_@types+pg@8.10.7_pg@8.11.3/node_modules/drizzle-orm/table" but cannot be named. [4053]

or is this better way to do optional join and having in query?

Expected behavior

No response

Environment & setup

No response

[dankochetov]

First of all, if you need conditional joins, you'd need to assign the .join() result to a new variable, because it'll have a different result type. To make it easier to use, you could wrap your conditional join into an IIFE, like this:

const query = (() => {
    const query = this.db.select(...).from(...);
    if (...) {
        return query.leftJoin(...);
    }
    return query;
})();
query.having(...);
const result = await query;
return result;

Secondly, the particular TS error you are getting might be related to the exports visibility issue, you could try updating to drizzle-orm@beta, it should be fixed there.

[Angelelz]

Also, I believe you should use const columns = getTableColumns(suppliers). Doing the catch all rest parameter is what got you that error. The symbols from Drizzle snuck in there.

[mdoeov]

First of all, if you need conditional joins, you'd need to assign the .join() result to a new variable, because it'll have a different result type. To make it easier to use, you could wrap your conditional join into an IIFE, like this:

const query = (() => {
  const query = this.db.select(...).from(...);
  if (...) {
      return query.leftJoin(...);
  }
  return query;
})();
query.having(...);
const result = await query;
return result;

Secondly, the particular TS error you are getting might be related to the exports visibility issue, you could try updating to drizzle-orm@beta, it should be fixed there.

drizzle-orm: ^0.29.0-a6dc062
drizzle-kit: ^0.20.2

I had the same problem, so I tried it too.

When checked with tosql(), the query appears to work normally.

However, in the result value, which is the last result above, some selected columns (table values ​​divided by conditional statements) appear as never or undefined.

It seems like you are not aware of the table inside the conditional statement.

Can this also be solved with drizzle-orm@beta update?

I am grateful for your confirmation.