Drizzle TeamDT
Drizzle Team•3y ago
SPS | Shootmail

Can't figure out how to design relational query

I have three tables
  • categories
  • products
  • media
I want to retrieve products that belong to a particular category, along with the product category and all images. The results should be paginated and should be filtered by category_slug, organization_id.

Relationships:
  1. Many products can belong to one category (products - categories = Many-to-one)
  2. Many products can have many images (products - images = Many-to-Many)
I have tried:
const productsQuery = db.query.categories.findMany({
            with: {
                productsOnCategory: {
                    with: {
                        mediaOnProducts: {
                            with: { media: true }
                        }
                    },
                    where: (products, {and, eq}) => and(eq(products.isVisible, 1), eq(products.organizationId, orgId))
                }
            },
            where: (categories, { and }) => and(eq(categories.slug, slug), eq(categories.isVisible, 1), eq(categories.organizationId, orgId))
        });

Problem: Cannot paginate the products using limit and offset as offset is only available for the top level relation which here is categories.

db.query.products.findMany({
            with: {
                category: {
                    
                }
            }
  });

Problem: Cannot filter on the basis of category_slug since 
where
is not available inside nested category I guess because this is a Many-to-one relation.

How can achieve the result I want using the relation queries?
Was this page helpful?