DT
Drizzle Teammcgrealife

`$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
Andrii Sherman
Andrii Sherman•355d ago
what database are you using?
mcgrealife
mcgrealife•355d 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
Andrii Sherman•355d ago
I guess it's because PlanetScale doesn't have a support for WITH statements I guess @Dan Kochetov knows exactly
bloberenober
bloberenober•355d ago
yes, CTEs are not supported on PS
mcgrealife
mcgrealife•355d 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?
Want results from more Discord servers?
Add your server
More Posts
Infer type for relational queryIs there anyway to infer the type of a relational queries result? For example if I have a user who in drizzle.config.ts: "Cannot use import statement outside a module"happens when I try to `push` ```ts import type { Config } from "drizzle-kit"; import { env } from "Error when using Drizzle (Non-abstract class 'PgSelect<TTableName, TSelection, TSelectMode, [...])I'm getting the following error when I try to use Drizzle: ```> graphql-server@1.0.0 start > npm runGenerate classes/interfaces from existing schemas?Messing around with drizzle, and I was wondering if there was any way to generate a class or interfaRelation Query - Get likes in postPlaying around with relational queries and I'm not quite getting how I'd retrieve the count of likesRelation query `extras` needs access to `with`I have a mysql table with two relations to another table. (one is for source values, one is for optiOptional filter param, coalesce to true?Is there a way to achieve this coalescing technique, but in valid drizzle sql? ```ts const filterByRelational query, Planetscale throws: `ResourceExhausted desc = Out of sort memory`SOLUTION: the problem was that I had a json column storing a massive value on each row. `code = ReInt to Float or Double db:pushHi we are working on a project that has drizzle db connected to a planetscale database. We initiallyTimestamp mode differences?What is the difference between the timestamp "mode" of "string'"and "date"?Many-to-Many Self RelationI'm looking to migrate from Prisma, but I'm having trouble trying to figure out how a many-to-many sRelations, three level nested where?Given a `User, Role, RoleToUser` many-to-many relation: ```ts export const userRelations = relation