Drizzle Team•2y ago

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 I guess because this is a Many-to-one relation. How can achieve the result I want using the relation queries?

7 Replies

Andrii Sherman•2y ago

I guess you need to query by products

db.query.products.findMany({
    with: {
      category: true,
      media: true
    },
    limit: 1,
    offset: 1
})

db.query.products.findMany({
    with: {
      category: true,
      media: true
    },
    limit: 1,
    offset: 1
})

SPS | ShootmailOP•2y ago

In this case, I will arrive at problem no 2 I have mentioned above. I can set the value for limit and offset but i cannot set filter for category slug.

Andrii Sherman•2y ago

Then if you want to use relational queries and filter by categories, seems like it's better to make 2 queries instead of 1

SPS | ShootmailOP•2y ago

Got it, that's what I ended up doing. Thanks!

icepeng•16mo ago

Hello @sps_storebud , I'm running into same issue you have faced.

Articles-Tags many-to-many, filter articles by tag.

Can you please share how you solved it with 2 queries?

SPS | ShootmailOP•16mo ago

Here is what i ended up doing

const countQuery = await db.select({ count: sql<number>`count(*)`, categoryId: categories.id }).from(products)
        .innerJoin(categories, eq(categories.id, products.categoryId))
        .where(and(eq(categories.isVisible, 1), eq(categories.slug, slug),eq(products.isVisible, 1), eq(categories.organizationId, orgId))).get();
        
        if(!countQuery){
            return err(ServiceClientException("Error ocurred while retrieving the total product count for category" + slug, c));
        }

        const productsQuery = db.query.products.findMany({
            with: {
                category: true,
                mediaOnProducts: {
                    with: { media: true }
                }
            },
            where: (products, { and, eq}) => and(eq(products.isVisible, 1), eq(products.organizationId, orgId), eq(products.categoryId, countQuery.categoryId)),
            offset: offset,
            limit: limit
        });

const countQuery = await db.select({ count: sql<number>`count(*)`, categoryId: categories.id }).from(products)
        .innerJoin(categories, eq(categories.id, products.categoryId))
        .where(and(eq(categories.isVisible, 1), eq(categories.slug, slug),eq(products.isVisible, 1), eq(categories.organizationId, orgId))).get();
        
        if(!countQuery){
            return err(ServiceClientException("Error ocurred while retrieving the total product count for category" + slug, c));
        }

        const productsQuery = db.query.products.findMany({
            with: {
                category: true,
                mediaOnProducts: {
                    with: { media: true }
                }
            },
            where: (products, { and, eq}) => and(eq(products.isVisible, 1), eq(products.organizationId, orgId), eq(products.categoryId, countQuery.categoryId)),
            offset: offset,
            limit: limit
        });

Sorry i am on phone so couldn't format correctly

icepeng•16mo ago

Thank you, helped a lot!

Gaming

Programming

Can't figure out how to design relational query