Using 'where' inside 'with'
Hello! I'm using schema like this:
export const user = pgTable('user', {
id: serial('id').primaryKey(),
username: text('username').notNull(),
email: text('email').notNull(),
password: text('password').notNull(),
bio: text('bio').default(null),
image: text('image').default(null),
});
export const userRelations = relations(user, ({ many }) => ({
article: many(article),
}));
export const article = pgTable('article', {
id: serial('id').primaryKey(),
slug: text('slug').notNull(),
title: text('title').notNull(),
description: text('description').default(null),
body: text('body').default(null),
createdAt: timestamp('createdAt').defaultNow(),
updatedAt: timestamp('updatedAt'),
tagList: text('tagList').notNull().array(),
favoritesCount: integer('favoritesCount').default(0),
authorId: integer('authorId').references(() => user.id),
});
export const articleRelations = relations(article, ({ one }) => ({
authorId: one(user, {
fields: [article.authorId],
references: [user.id],
}),
}));
Next step, I wanna use this query:
const articlesQuery = await this.db.query.article.findMany({
with: {
authorId: {
where: (authorId) => eq(authorId.username, queryParam.author),
},
},
But I got TS-error:
error TS2322: Type '{ where: (authorId: any) => SQL<unknown>; }' is not assignable to type 'true | { columns?: { id?: boolean; username?: boolean; email?: boolean; password?: boolean; bio?: boolean; image?: boolean; }; with?: { article?: true | DBQueryConfig<"many", false, ExtractTablesWithRelations<typeof import("D:/Site/mediumclone_nestjs/src/schema")>, { ...; }>; }; extras?: Record<...> | ((fields: { .....'.
As I understand it, for some reason it is not possible to use where inside with, however in the example on the site it works fine. Could you please tell me what my mistake is?5 Replies
also running into this, i swear this used to work
Doesn't this query make more sense doing it the other way around?
for me i need a where on both tables so inverting the query won't work
sure i could just filter the results after the fact but it seems wasteful when the db should be able to do this itself
The API specification needs the response to look like this:
{
"id": 37,
"slug": "article-first-kmisni",
"title": "Article first",
"description": "Article descr",
"body": "Article body",
"createdAt": "2023-11-21T09:41:50.275Z",
"updatedAt": null,
"tagList": [],
"favoritesCount": 0,
"author": {
"id": 35,
"username": "john",
"email": "[email protected]",
"bio": null,
"image": null
}
}
So we should get articles, and inside each article there should be information about its author.
As a temporary solution, I did via selectDistinct(bellow), but I'd still like to figure out why where inside with doesn't want to trigger like in the documentation.
const articles = await this.db
.selectDistinct({
id: article.id,
slug: article.slug,
title: article.title,
description: article.description,
body: article.body,
createdAt: article.createdAt,
updatedAt: article.updatedAt,
tagList: article.tagList,
favoritesCount: article.favoritesCount,
author: {
id: user.id,
username: user.username,
email: user.email,
bio: user.bio,
image: user.image,
},
})
.from(article)
.where(
queryParam?.author && eq(user.username, queryParam?.author)
)
.leftJoin(user, eq(article.authorId, user.id))
.limit(query?.limit)
.offset(query?.offset)
.orderBy(desc(article.createdAt));
If this is the shape you need, your solution is the least wonderful
It doesn't make sense to use the RQB if you don't need to aggregate data