Drizzle Team•8mo ago

advanced relational query builder

hey, i have a use case where i need to return rows from one table based on filters requiring on connected table here's prisma equivalent code

await prisma.card.findMany({
  where: {
    // character is another table
    character: {
      element: '...'
    }
  }
})

await prisma.card.findMany({
  where: {
    // character is another table
    character: {
      element: '...'
    }
  }
})

how would this be possible with drizzle

10 Replies

Mykhailo•8mo ago

Hello, @Oreki! You can do something like this:

await db.query.users.findFirst({
  where: eq(users.id, 1),
  columns: {
    name: true,
  },
  with: {
    // relation
    usersToGroups: {
      with: {
        // relation
        group: {
          columns: {
            id: true,
            title: true,
          },
        },
      },
    },
  },
});

await db.query.users.findFirst({
  where: eq(users.id, 1),
  columns: {
    name: true,
  },
  with: {
    // relation
    usersToGroups: {
      with: {
        // relation
        group: {
          columns: {
            id: true,
            title: true,
          },
        },
      },
    },
  },
});

Oreki•8mo ago

yeah but that is not what i want to do, in that particular example i want all the cards who have a relation to characters table and the respective character's element is lets say for example fire

Mykhailo•8mo ago

ok, as I see you want to get cards with characters relation and filter by character element here we get posts with comments relation and filter by comment createdAt

 const result = await db.query.posts.findMany({
    with: {
      comments: {
        where: (comments, { lt }) => lt(comments.createdAt, new Date()),
      },
    },
  });

 const result = await db.query.posts.findMany({
    with: {
      comments: {
        where: (comments, { lt }) => lt(comments.createdAt, new Date()),
      },
    },
  });

Oreki•8mo ago

i tried that as well, only if i had a where key in my relational query builder

Mykhailo•8mo ago

could you please provide your query code?

Oreki•8mo ago

yes gimme a sec

context.database.query.cards.findMany({
      where: () => eq(cards.ownerId, user.raw.id),
      orderBy: () =>
        sort ? sortOptions[sort as keyof typeof sortOptions] : [],
      limit: 10,
      offset,
      with: {
        character: {
          
        },
      },
    }),

context.database.query.cards.findMany({
      where: () => eq(cards.ownerId, user.raw.id),
      orderBy: () =>
        sort ? sortOptions[sort as keyof typeof sortOptions] : [],
      limit: 10,
      offset,
      with: {
        character: {
          
        },
      },
    }),

Oreki•8mo ago

Oreki•8mo ago

i don't get a where key for some reason a card can only have one character, but a character can have many different cards

Mykhailo•8mo ago

@Oreki got it, there was a twit with a similar problem. You have to change a bit your query, because you can’t filter character because it’s many to one. https://twitter.com/Jaaneek/status/1749025589208519062 You had to query by character first and then look for cards because it’s one to many. something like this, but unfortunately there is no offset there

db.query.character.findFirst({
    with: {
      cards: {
        where: () => eq(cards.ownerId, user.raw.id),
        orderBy: () => (sort ? sortOptions[sort as keyof typeof sortOptions] : []),
        limit: 10,
      },
    },
  }),

db.query.character.findFirst({
    with: {
      cards: {
        where: () => eq(cards.ownerId, user.raw.id),
        orderBy: () => (sort ? sortOptions[sort as keyof typeof sortOptions] : []),
        limit: 10,
      },
    },
  }),

Miłosz Jankiewicz (@Jaaneek) on X

@DrizzleORM Sure: schema https://t.co/mbQu9WjkP2 My current query that is working: https://t.co/0yYq6dTlrT Query I was trying to make: https://t.co/YRmNegpnih

Twitter

Oreki•8mo ago

Hmm I thought so as well, thanks

Gaming

Programming

advanced relational query builder