How many times object is being nested? (N+1, Eager Loading etc.)

Hello everyone. I’ve got problems about JSON objects that are nested.

const advertisement = await Advertisement.find(params.advertisement_id)
await advertisement.load('category')

const jason = advertisement.toJSON()
let cat = await Category.find(jason.category.id)

let count=0
    while (true) {
      if (cat.parent_id != null) {
        cat = await Category.find(cat.parent_id)
        count++
      } else {
        break
      }
    }
jason.category = cat

return response.status(200).json({
      data: jason
})

For example, I used this block for nested count. While loop runs until category object’s parent_id is null but this is not useful because of there are many queries. I think this is N+1 problem, I guess. And when I use this code I get this response:

{
    "data": {
        "id": 1,
        "user_id": 1,
        "title": "Doktordan",
        "content": "Temiz",
        "category_id": 7,
        "created_at": "2020-05-19 23:57:26",
        "updated_at": "2020-05-19 23:57:26",
        "category": {
            "id": 2,
            "title": "Vasıta",
            "parent_id": null,
            "created_at": "2020-05-19 23:42:00",
            "updated_at": "2020-05-19 23:42:00",
            "subcategories": [
                {
                    "id": 5,
                    "title": "Otomobil",
                    "parent_id": 2,
                    "created_at": "2020-05-19 23:42:27",
                    "updated_at": "2020-05-19 23:42:27",
                    "subcategories": []
                },
                {
                    "id": 6,
                    "title": "Kamyon",
                    "parent_id": 2,
                    "created_at": "2020-05-19 23:42:33",
                    "updated_at": "2020-05-19 23:42:33",
                    "subcategories": [
                        {
                            "id": 7,
                            "title": "Temiz",
                            "parent_id": 6,
                            "created_at": "2020-05-19 23:42:55",
                            "updated_at": "2020-05-19 23:42:55"
                        },
                        {
                            "id": 8,
                            "title": "Kötü",
                            "parent_id": 6,
                            "created_at": "2020-05-19 23:43:02",
                            "updated_at": "2020-05-19 23:43:02"
                        }
                    ]
                }
            ]
        }
    }
}

parent

This is an example category table in MySQL, some rows’ve got parent_id. This table is going to be very big and also complicated.

For example, I’ve got an advertisement that its category_id is 7. 7’s parent_id is 6 and 6’s is 2 and 2’s is null. Nested count would be 2 (7=>6=>2). How can I find out this nesting count? I’m asking this.

I’ve used eager loading for this problem.

await cat.load('subcategories'.concat('.subcategories'.repeat(count-1)))

But then, I needed to know nested count value for loading subcategories. If I don’t know this count, I can’t load subcategories too because of I do not know that how much it nested to be.

How can I figure out that how many times object is being nested?

1 Like

I did not get the complete picture from the post - but are you trying to get parent of categories of the given advertisement id?

If yes, one of the ways to do that will be -

const adv = await Advertisement.query()
        .where("id", params.advertisement_id)
        .with("category")
        .fetch();

If you are trying to get subcategories from a category -

  1. Is it possible to map subcategory.advertisement_id to the given advertisement_id?
    If yes, just establish relationship to the subcategory from advertisement model -
const adv = await Advertisement.query()
        .where("id", params.advertisement_id)
        .with("category")
        .with("subcategory")
        .fetch();
  1. If such a relationship is not possible, I would collect category ids (can’t be more than 500-1000 per advertisement, I hope) in an array and use that array to query for subcategories using an in query
2 Likes

Thanks, but this solved my problem: Answer