DT
Drizzle Team•2y 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))
        });
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
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 | Shootmail
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
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 | Shootmail
SPS | ShootmailOP•2y ago
Got it, that's what I ended up doing. Thanks!
icepeng
icepeng•2y 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 | Shootmail
SPS | ShootmailOP•2y 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
icepeng•2y ago
Thank you, helped a lot!

Did you find this page helpful?