Drizzle Team•2y ago

`$with` example in docs error "unsupported: with expression in select statement"

I have a user with id of 42, just like the docs https://orm.drizzle.team/docs/crud#with-clause but executing the same query throws an Error: unsupported: with expression in select statement

const sq = db.$with('sq').as(db.select().from(users).where(eq(users.id, 42)))
const result = await db.with(sq).select().from(sq)

const sq = db.$with('sq').as(db.select().from(users).where(eq(users.id, 42)))
const result = await db.with(sq).select().from(sq)

The $with clause looks superb.

Quering with SQL-like syntax [CRUD] – DrizzleORM

Drizzle ORM | %s

5 Replies

Andrii Sherman•2y ago

what database are you using?

mcgrealifeOP•2y ago

I am using mysql via planetscale (sorry i'll mention next time) Note: the nearly identical subquery (without the $with) in the docs right below the $with section does work.

const sq = db.select().from(users).where(eq(users.id, 42)).as('sq');
const result = await db.select().from(sq);

const sq = db.select().from(users).where(eq(users.id, 42)).as('sq');
const result = await db.select().from(sq);

https://orm.drizzle.team/docs/crud#select-from-subquery

Quering with SQL-like syntax [CRUD] – DrizzleORM

Drizzle ORM | %s

Andrii Sherman•2y ago

I guess it's because PlanetScale doesn't have a support for WITH statements I guess @Dan Kochetov knows exactly

Dan•2y ago

yes, CTEs are not supported on PS

mcgrealifeOP•2y ago

Ah yes, I see now that error is from PlanetScale, not drizzle. I should have searched first https://github.com/planetscale/discussion/discussions/270 Thanks to both 🙌

GitHub

unsupported: with expression in select statement · planetscale disc...

Hi, When running a recursive CTE, I get the following error message. unsupported: with expression in select statement Is PlanetScale able to or planning support recursive CTEs?

Gaming

Programming

`$with` example in docs error "unsupported: with expression in select statement"