DT
Drizzle Team•3w ago
brynthrax

Nested relation filtering

We have a user table (id, name, role…) and section table (id, name…), bridged by a user_section table (userId, sectionId). Hence, a many-to-many relationship. How can I accomplish in drizzle wherein I query for a section, including users (and its fields) under it, but only users with the role “Student”? Right now, I can only filter through userId and sectionId in the relational query API (see screenshot).
No description
6 Replies
Mario564
Mario564•3w ago
This is a limitation of the current version of RQB, where you can only filter by the main table you're querying (in this case, that's the user table). We will be addressing this in RQB v2, for now, you'll have to use the SQL-like query builder syntax If you can provide me the desired query in RQB, I can help you translate it into the query builder syntax if you need help with that
Tomato
Tomato•3w ago
Excause me, May i know when you release this ? for now, i really need to put where condition in relation to do soft delete. Thank you
Mario564
Mario564•3w ago
Expect RQB v2 to release sometime early next year
Patrik
Patrik•3w ago
so instead of this 
await db.query.portfolioFavouritesTable.findMany({
   columns: {},
   with: {
      portfolioCase: {
         columns: {
            id: true,
            slug: true,
            color: true,
            status: true
         },
      },
   },
   where: eq(portfolioFavouritesTable.userUid, userID),
   orderBy: (favs, { desc }) => [desc(favs.createdAt)]
})
await db.query.portfolioFavouritesTable.findMany({
   columns: {},
   with: {
      portfolioCase: {
         columns: {
            id: true,
            slug: true,
            color: true,
            status: true
         },
      },
   },
   where: eq(portfolioFavouritesTable.userUid, userID),
   orderBy: (favs, { desc }) => [desc(favs.createdAt)]
})
we need to use this? 
await db
   .select({
      id: portfolioTable.id,
      slug: portfolioTable.slug,
      color: portfolioTable.color,
      status: portfolioTable.status,
   })
   .from(portfolioTable)
   .innerJoin(portfolioFavouritesTable, eq(portfolioFavouritesTable.portfolioId, portfolioTable.id))
   .where(
      and(
         eq(portfolioFavouritesTable.userUid, userID),
         eq(portfolioTable.status, 'published')
      )
   )
   .orderBy(desc(portfolioFavouritesTable.createdAt))
await db
   .select({
      id: portfolioTable.id,
      slug: portfolioTable.slug,
      color: portfolioTable.color,
      status: portfolioTable.status,
   })
   .from(portfolioTable)
   .innerJoin(portfolioFavouritesTable, eq(portfolioFavouritesTable.portfolioId, portfolioTable.id))
   .where(
      and(
         eq(portfolioFavouritesTable.userUid, userID),
         eq(portfolioTable.status, 'published')
      )
   )
   .orderBy(desc(portfolioFavouritesTable.createdAt))
Mario564
Mario564•3w ago
Yes
Patrik
Patrik•3w ago
thx

Did you find this page helpful?