I have a category module in a hierarchy. I need to query that category by checking if any one of the parents should not be inactive(false).

For example

1. First category (inactive or false) 1.1 > First Category Child 1.1.1> First Category Grand Child

My Query

$category = Category::where('status',true)->where('title','like','%'.$keywords.'%')->paginate(10);

This query only checks if its status is true or false. But i need to check status of its parent level as well. So, is there a way to check parent level status as well inside the query. The category may have parent or maynot have parent as well. So, because of this whereHas('parents') doesn't work.

CodePudding user response：

you can use whereHas method by giving the relation name:

$category = Category::where('status',true)->whereHas('parent', function ($q) {
$q->where('status', true)->whereHas('parent', function ($query) {
$query->where('status', true)});
})->where('title','like','%'.$keywords.'%')->paginate(10);

CodePudding user response：

There is, assuming you category model looks like this.

class Category extends Model {
    public function parent() {
        return $this->belongsTo(self::class, 'parent_id');
    }
}

Then you can utilize whereHas(), provide a closure that checks for your condition and query your condition in that relationship. To handle the condition of no parents, check if it has parent with correct status or if it does not have any parents.

Category::where('status',true)
    ->where(funcion ($query) {
        $query->whereHas('parent', function ($query) {
            $query->where('status', true);
        })->orDoesntHave('parent');
    })->get();

For multiple nesting you could solve it by keeping adding the condition. It is not optimal, but at query time you don't know the nesting.

->where(funcion ($query) {
    $query->whereHas('parent.parent', function ($query) {
        $query->where('status', true);
    })->orDoesntHave('parent.parent');
})->where(funcion ($query) {
    $query->whereHas('parent.parent.parent', function ($query) {
        $query->where('status', true);
    })->orDoesntHave('parent.parent.parent');
})

An alternative solution in the long run, could be making logic that updates the status recursively in all categories when they get the status changed.