How SQL get nested relation item with limit pagination

I need get a products which under Category but with a limit of products item for paginate purpose. I am use strapi headlessCMS for my backend. i will query it from "slug" in category. And then it will return collection as below code, but i need a LIMIT for my products list.

Since Strapi is use bookshelf.js in sql. i have tried it as exmaple 2 below. I am bad in handle data, hope someone sharing idea how to do or give some solution for me. Appreciate =).

If you know in raw sql statement, please sharing with me. I'll do the search for it. The important part is given a logically idea how to do with data. So, my brain has the map can do the searching. Thanks.

Model relationship: Category hasMany Products

Example 1

const res = await strapi.query("category").findOne({ slug });

// return
{
id: 2,
slug: 'slug-string'
....
products: [
{
id: 1,
name: 'Ice Cream',
category: 2
},
{
id: 2,
name: 'Bread',
category: 2
},
....
{
id: 100,
name: 'Paper',
category: 2
}
]
}


Example 2: i guess this is bad practice ya? Because it query all products, the better way i think is example 1.

const Product = strapi.query("product").model;

const result = await Product.query((qb) => {
qb.where("category", 2);
}).fetchPage({
pageSize: 1,
page: start, // start is a number, it will pass from front end query page=2
});

Continue reading...