Transform sql query into query for adonis

I have 3 tables products, categories, product_categories . I have a query SQL that returns 10 products from each category, I needed it for adonis now, but I don’t know how I can do it

Query SQL

SELECT *
FROM
(
SELECT
ROW_NUMBER() OVER(PARTITION BY “product_categories”.“idCategory” ORDER BY “product_categories”.“idCategory”) AS INDICE,
“products”.*,
“product_categories”.“idCategory” AS “idCategory”
FROM “products”
INNER JOIN “product_categories” ON “products”.“id” = “product_categories”.“idProduct”
WHERE “product_categories”.“idCategory” IN (select “categories”.“id” from “categories”)
) AS X
WHERE X.INDICE <= 3

I tried to do this with adonis, but the only way was to select for each category

Hello @EfraimFelix

It depends on how your relations are set up, but you could so something like this:

let data = await Category
  .query()
  .with('products', (builder) => {
    builder.limit(10)
  })
  .fetch()

This will get you all categories and 10 products in there. If you want to modify more what products will be returned you can add more stuff to builder. Like builder.orderBy('id').limit(10) etc

For setting up sich relation looks like many to many https://adonisjs.com/docs/4.1/relationships#_belongs_to_many
would be the one to use