DT
Drizzle Team•5mo ago
Deen24ID

Issues from a WITH clause (CTE).

I have the following postgreSQL schema: 
export const price = pgTable("price", {
  id: uuid().primaryKey().defaultRandom().notNull(),
  name: varchar({ length: 255 }).notNull(),
  buy: integer().notNull(),
  sell: integer().notNull(),
  lastUpdatedAt: timestamp({ withTimezone: true, mode: "string" })
    .defaultNow()
    .notNull(),
});
export const price = pgTable("price", {
  id: uuid().primaryKey().defaultRandom().notNull(),
  name: varchar({ length: 255 }).notNull(),
  buy: integer().notNull(),
  sell: integer().notNull(),
  lastUpdatedAt: timestamp({ withTimezone: true, mode: "string" })
    .defaultNow()
    .notNull(),
});
In the entries the same name can be repeated in many rows. For each name, I just want to keep the rows with the highest lastUpdatedAt, keeping other columns along. I can get these values using the following SQL query: 
WITH priceLast as (SELECT name, MAX(price."lastUpdatedAt") as lastUpdatedAt FROM price GROUP BY name)
SELECT 
  price.id as id, 
  priceLast.name as name, 
  priceLast.lastUpdatedAt as lastUpdatedAt,
  price.buy as buy,
  price.sell as sell
FROM priceLast INNER JOIN price ON
priceLast.lastupdatedat = price."lastUpdatedAt" AND priceLast.name = price.name
WITH priceLast as (SELECT name, MAX(price."lastUpdatedAt") as lastUpdatedAt FROM price GROUP BY name)
SELECT 
  price.id as id, 
  priceLast.name as name, 
  priceLast.lastUpdatedAt as lastUpdatedAt,
  price.buy as buy,
  price.sell as sell
FROM priceLast INNER JOIN price ON
priceLast.lastupdatedat = price."lastUpdatedAt" AND priceLast.name = price.name
But, I can't rewrite this in drizzle. Any help is greatly appreciated. Thanks!
4 Replies
Sillvva
Sillvva•5mo ago
https://orm.drizzle.team/docs/select#with-clause 
import { max } from "drizzle-orm";

const cte = db
    .select({
        name: price.name,
        lastUpdatedAt: max(price.lastUpdatedAt).as('lastUpdatedAt')
    })
    .from(price)
    .groupBy(price.name);
const priceLast = db.$with("priceLast").as(cte);
const res = await db
    .with(priceLast)
    .select({
        id: price.id,
        name: priceLast.name,
        lastUpdatedAt: priceLast.lastUpdatedAt,
        buy: price.buy,
        sell: price.sell
    })
    .from(priceLast)
    .innerJoin(price, and(eq(priceLast.name, price.name), eq(priceLast.lastUpdatedAt, price.lastUpdatedAt)));
import { max } from "drizzle-orm";

const cte = db
    .select({
        name: price.name,
        lastUpdatedAt: max(price.lastUpdatedAt).as('lastUpdatedAt')
    })
    .from(price)
    .groupBy(price.name);
const priceLast = db.$with("priceLast").as(cte);
const res = await db
    .with(priceLast)
    .select({
        id: price.id,
        name: priceLast.name,
        lastUpdatedAt: priceLast.lastUpdatedAt,
        buy: price.buy,
        sell: price.sell
    })
    .from(priceLast)
    .innerJoin(price, and(eq(priceLast.name, price.name), eq(priceLast.lastUpdatedAt, price.lastUpdatedAt)));
@Deen24ID pinging for notification
Deen24ID
Deen24IDOP•5mo ago
Error: You tried to reference "lastUpdatedAt" field from a subquery, which is a raw SQL field, but it doesn't have an alias declared. Please add an alias to the field using ".as('alias')" method.
Error: You tried to reference "lastUpdatedAt" field from a subquery, which is a raw SQL field, but it doesn't have an alias declared. Please add an alias to the field using ".as('alias')" method.
I get the above error message.
Sillvva
Sillvva•5mo ago
Oh, add .as() to the max function. max(schema.price.lastUpdatedAt).as("lastUpdatedAt")
To select arbitrary SQL values as fields in a CTE and reference them in other CTEs or in the main query, you need to add aliases to them: 
const sq = db.$with('sq').as(db.select({ 
  name: sql<string>`upper(${users.name})`.as('name'),
})
.from(users));

const result = await db.with(sq).select({ name: sq.name }).from(sq);
const sq = db.$with('sq').as(db.select({ 
  name: sql<string>`upper(${users.name})`.as('name'),
})
.from(users));

const result = await db.with(sq).select({ name: sq.name }).from(sq);
If you don’t provide an alias, the field type will become DrizzleTypeError and you won’t be able to reference it in other queries. If you ignore the type error and still try to use the field, you will get a runtime error, since there’s no way to reference that field without an alias.
Updated the above query
Deen24ID
Deen24IDOP•5mo ago
Thanks! It works!

Did you find this page helpful?