DT
Drizzle Team•12mo ago
finn

`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
finn
finn•12mo 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?
finn
finn•12mo ago
finn
finn•12mo ago
finn
finn•12mo 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
finn
finn•12mo ago
Want results from more Discord servers?
Add your server
More Posts
IS IT POSSIBLE: to use a relation field in a where clause?Anyone knows if it's possible to have a nested relation in a where clause?Is there a way to save the generated response Type from a query?I see that when you hover over the type for the response of a query, you can see what data will be rOrdering by relation in relational queryI am trying to sort on an attribute of a relation using the relational queries api, but as far as I Nested Schema, Helper FunctionsHi, I am new to Drizzle. 1. I have the following User schema and I was wondering is there any way types from nested relational querieswondering what the type of `user` is? ```ts async function getUserWithSettings(steamId: string): Prodrizzle-kit@win still not working. What am I doing wrong?`/Users/alexanderniebuhr/Developer/Projects/alexanderniebuhr_blog/packages/db/drizzle.config.ts` ```SQLITE_ERROR: near "ilike": syntax errorI've currently setup an my SQL query as follows: ```ts export const getObservationsByUserId = asyncComposite Primary Key from Foreign Keys```ts export const userItemsHas = pgTable('user_items_has', { postId: uuid('post_id').references((Introspect command failingI'm setting up Drizzle for the first time, I'm trying to get the introspect command to run with mysqError while push:sqlite after updateI am getting this error while using ```npx drizzle-kit push:sqlite``` here is drizzle.config.json `