8 replies

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))
        });

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: {
                    
                }
            }
  });

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

Problem: Cannot filter on the basis of category_slug since

where

where

is not available inside nested

category

category

I guess because this is a Many-to-one relation.

How can achieve the result I want using the relation queries?

Can't figure out how to design relational query

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))
        });

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: {
                    
                }
            }
  });

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

Problem: Cannot filter on the basis of category_slug since

where

where

is not available inside nested

category

category

I guess because this is a Many-to-one relation.

How can achieve the result I want using the relation queries?

Can't figure out how to design relational query

Similar Threads

Can't figure out how to design relational query

Similar Threads

Similar Threads

Similar Threads