get all products from parent category and all it's sub-categories Adonis V5

[aymanatmeh]

Here's my categories table :

id	parent_id	name
1	NULL	Electronics
2	1	Computers
3	2	Accessories
4	3	Keyboards

and here's my products-category Pivot table :

id	category_id	product_id
1	1	1
2	2	1
3	3	1

How can i setup my category model and product model -> recursive model
to get product with its assigned categories and subCategories (childs) ?

[HappyLDE]

Also interested in this.

I know with Laravel it goes like this (look at the accepted answer):
https://stackoverflow.com/questions/26652611/laravel-recursive-relationships

I show this solution because I come from Laravel and would like to do this in Adonisjs (Am using V5)

// Relationship in model
public function childrenAccounts()
{
    return $this->hasMany('Account', 'act_parent', 'act_id');
}

public function allChildrenAccounts()
{
    return $this->childrenAccounts()->with('allChildrenAccounts');
}

[jankrnac]

Hi, I am interested in recursive parent - child relationships also
Does anyone have some idea please?

What I have now for typical ecommerce category model:

@hasMany(() => Category, {
    foreignKey: 'parentId',
    localKey: 'id'
})
public children: HasMany<typeof Category>

and in controller

const categories = await Category.query().whereNull('parent_id').preload('children', (childrenQuery) => {
    childrenQuery.preload('children')
})

but this solution only fetch root - children - children .... ideally we want to fetch all levels of children recursively

[aymanatmeh]

CategoriesController Preview Image
you can use queryBuilder instead of if condition

in Category Model
public subCategory: HasMany<typeof Category>

[thetutlage]

I have a working solution for this. Planning to create a polished example and blog about it this week.

[Thrillhop]

Hi @thetutlage,

I was wondering if you did come up with the polished example about recursive queries; I've run into the same issue with the following structure: Chatroom 1:M Messages (1:M User 1:M Profile) 1:M Messages... I got it to work with a one-level deep query but going beyond there would require recursion:

const messageParents = await Message.query()
            .where('room_id', id)
            .has('children')
            .preload('children', (childrenQuery) => {
                childrenQuery.orderBy('created_at', 'asc')
                childrenQuery.preload('user', (userQuery) => {
                    userQuery.preload('profile')
                })
                // now I'd like to get children -> children -> ...
            })
            .preload('user', (userQuery) => {
                userQuery.preload('profile')
            })

[jankrnac]

@thetutlage I am also interested in your polished solution :)

[brojor]

Hi @thetutlage I'd really like to know the solution too. May I ask how it's going to be published?

[berlinshawn063]

import { DateTime } from 'luxon'
import { BaseModel, column } from '@IOC:Adonis/Lucid/Orm'

export default class Post extends BaseModel {
@column({ isPrimary: true })
public id: number

@column()
public title: string

@column()
public content: string

@column()
public status: string

@column.dateTime({ autoCreate: true })
public createdAt: DateTime

@column.dateTime({ autoCreate: true, autoUpdate: true })
public updatedAt: DateTime

[manassegitau]

Do we have a solution to this yet? I have tried to research and I am unable to find it.

[HappyLDE]

Yes, I found the solution. How many subcategories are there? Usually on ECOM it should be the main cat and two subs MAX. With that, you can preload it as it's not much and predictible, like so :

const categories = await Category.preload('children', (queryChildren) => {
      queryChildren.preload('children', (queryChildren) => {
        queryChildren.preload('children')
      })
    }).query().where('id', mainCategoryId).first()

Then make nested loop to build a categoriesIDs array to then use on the products with whereIn('category_id', categoriesIDs).

If on the other hand you have different cats with many subs (unpredictible), then you need to make a recursive function that will call iself.

This is pseudocode :

const category = await Category.query().where('id', mainCategoryId).first()

public async recursivelyBuildCategoriesArray(category, categoriesIDs) {
    await category.load('children')
    for ( let childCategory of category.children ) {
      categoriesIDs.push(childCategory.id)
      recursivelyBuildCategoriesArray(childCategory, categoriesIDs)
    }
  }

[manassegitau]

This is great. Thank You.
I think there should be a simpler way to do it at the model level so that you just preload the relationship. Hopefully, that will be possible in future versions. Or maybe it does exist only that we are yet to discover it.