Drizzle Team•2y ago

`where` within `where`? confused about complex query

export const posts = pgTable('posts', {
  id: uuid('id').defaultRandom().primaryKey(),
  userId: char('user_id', { length: 17 }).notNull().references(() => users.id),
  description: text('description'),
  createdAt: timestamp('created_at', { withTimezone: true }).defaultNow().notNull(),
});

export const postsRelations = relations(posts, ({ one, many }) => ({
  user: one(users, {
    fields: [posts.userId],
    references: [users.id],
  }),
  userItems: many(postUserItems),
}));

export const userItems = pgTable('user_items', {
  assetId: varchar('asset_id', { length: 255 }).primaryKey(),
  userId: char('user_id', { length: 17 }).notNull().references(() => users.id),
  itemId: serial('item_id').references(() => items.id).notNull(),
  imageUrl: varchar('image_url', { length: 512 }).notNull(),
});

export const userItemsRelations = relations(userItems, ({ one, many }) => ({
  user: one(users, {
    fields: [userItems.userId],
    references: [users.id],
  }),
  item: one(items, {
    fields: [userItems.itemId],
    references: [items.id],
  }),
  posts: many(trades),
  postUserItems: many(postUserItems),
}));

export const postUserItems = pgTable('post_user_items', {
  postId: uuid('post_id').references(() => posts.id),
  assetId: varchar('asset_id', { length: 255 }).references(() => userItems.assetId),
},(table) => {
  return {
    pk: primaryKey(table.postId, table.assetId),
  };
});

export const postUserItemsRelations = relations(postUserItems, ({ one, many }) => ({
  post: one(posts, {
    fields: [postUserItems.postId],
    references: [posts.id],
  }),
  userItem: one(userItems, {
    fields: [postUserItems.assetId],
    references: [userItems.assetId],
  }),
}));

export const posts = pgTable('posts', {
  id: uuid('id').defaultRandom().primaryKey(),
  userId: char('user_id', { length: 17 }).notNull().references(() => users.id),
  description: text('description'),
  createdAt: timestamp('created_at', { withTimezone: true }).defaultNow().notNull(),
});

export const postsRelations = relations(posts, ({ one, many }) => ({
  user: one(users, {
    fields: [posts.userId],
    references: [users.id],
  }),
  userItems: many(postUserItems),
}));

export const userItems = pgTable('user_items', {
  assetId: varchar('asset_id', { length: 255 }).primaryKey(),
  userId: char('user_id', { length: 17 }).notNull().references(() => users.id),
  itemId: serial('item_id').references(() => items.id).notNull(),
  imageUrl: varchar('image_url', { length: 512 }).notNull(),
});

export const userItemsRelations = relations(userItems, ({ one, many }) => ({
  user: one(users, {
    fields: [userItems.userId],
    references: [users.id],
  }),
  item: one(items, {
    fields: [userItems.itemId],
    references: [items.id],
  }),
  posts: many(trades),
  postUserItems: many(postUserItems),
}));

export const postUserItems = pgTable('post_user_items', {
  postId: uuid('post_id').references(() => posts.id),
  assetId: varchar('asset_id', { length: 255 }).references(() => userItems.assetId),
},(table) => {
  return {
    pk: primaryKey(table.postId, table.assetId),
  };
});

export const postUserItemsRelations = relations(postUserItems, ({ one, many }) => ({
  post: one(posts, {
    fields: [postUserItems.postId],
    references: [posts.id],
  }),
  userItem: one(userItems, {
    fields: [postUserItems.assetId],
    references: [userItems.assetId],
  }),
}));

how can i select posts that contain a userItems with assetId = {X}?

5 Replies

finnOP•2y ago

const assetId: number = 1;
const assetIdPosts = await db.query.posts.findMany({
  // ...
});

const assetId: number = 1;
const assetIdPosts = await db.query.posts.findMany({
  // ...
});

the closest i've got is

const assetIdPosts = await db.query.posts.findMany({
  with: {
    userItems: {
      where: (userItem, { eq }) => eq(userItem.assetId, assetId),
    },
  },
});

const assetIdPosts = await db.query.posts.findMany({
  with: {
    userItems: {
      where: (userItem, { eq }) => eq(userItem.assetId, assetId),
    },
  },
});

this retrieves every post, but only the userItems where its assetId = 1 which is not what i need also i need to sort by posts.createdAt maybe one of these can help?

finnOP•2y ago

https://orm.drizzle.team/docs/views

Views (WIP) – DrizzleORM

Drizzle ORM | %s

finnOP•2y ago

https://orm.drizzle.team/docs/sql

Magical sql operator 🪄 – DrizzleORM

Drizzle ORM | %s

finnOP•2y ago

i need to return posts based on other queries too so something "rigid" like these seems good maybe something like

where: (table, { sql }) => (sql`json_array_length(${table.userIitems}) > 0`),

where: (table, { sql }) => (sql`json_array_length(${table.userIitems}) > 0`),

kinda just making stuff up at this point

finnOP•2y ago

https://i.stack.imgur.com/LiZGb.png

Gaming

Programming

`where` within `where`? confused about complex query