Drizzle Team•2y ago

nested relation queries

userPost: {
  id,
  description,
  createdAt,
  expiresAt,
  user: {
    id,
    premium,
  },
  userItems: [{
    assetId
    imageUrl,
    item: {
      id,
      name,
      special,
      rarity,
    }
  },],
  items: [{
    id,
    name,
    special,
    rarity,
    imageUrl
  },],
}

userPost: {
  id,
  description,
  createdAt,
  expiresAt,
  user: {
    id,
    premium,
  },
  userItems: [{
    assetId
    imageUrl,
    item: {
      id,
      name,
      special,
      rarity,
    }
  },],
  items: [{
    id,
    name,
    special,
    rarity,
    imageUrl
  },],
}

3 Replies

finnOP•2y ago

given a userPost is comprised of these fields and this relational database how can i make queries like these "pseudo SQL" ones below?

// 1
select all `userPosts` where any `item` linked to a `userPost.userItems` in this post has `id = {something}`

// 2
select all `userPosts` where any `item` in `userPost.items` has `id = {something}`

// 3
select all `userPosts` by some user `id = {something}`

// 4
insert a userPost

// 5
delete a userPost

// 6
delete a user and all information associated with them

// 1
select all `userPosts` where any `item` linked to a `userPost.userItems` in this post has `id = {something}`

// 2
select all `userPosts` where any `item` in `userPost.items` has `id = {something}`

// 3
select all `userPosts` by some user `id = {something}`

// 4
insert a userPost

// 5
delete a userPost

// 6
delete a user and all information associated with them

const userPosts = await db.query.posts.findMany({ // ... });

const userPosts = await db.query.posts.findMany({ // ... });

i want to sort userPosts by createdAt, and only return limit: 50

finnOP•2y ago

not sure if these can help? https://orm.drizzle.team/docs/sql https://orm.drizzle.team/docs/views

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

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

Magical sql operator 🪄 – DrizzleORM

Drizzle ORM | %s

Views (WIP) – DrizzleORM

Drizzle ORM | %s

finnOP•2y ago

i'm particularly concerned about the first 3 queries

Gaming

Programming

nested relation queries