how to get sub category relation items

I have a categories table with nested categories(parent_id), and in an api endpoint when user gives me a category id I should show them sub root category names of that category with files of each category, but it should also contain files of inner child categories of that category.

tykus

1 year ago

Level 104

Check your Staudenmeir’s Adjacency package, and specifically the hasManyOfDescebdentsAndSelf relationship method

amir5

1 year ago

Level 7

@tykus How to limit file count? I have a search scope that works fine, but limit doesn't work(returns more than the limit):

$categoryQuery->with('decendantFiles', function (MorphToManyOfDescendants $q) use ($request) {
$q->limit(10)->search(request('q'));
});

and in category model:

    public function decendantFiles(): MorphToManyOfDescendants
    {
        return $this->morphedByManyOfDescendants(File::class, 'categoriable', 'category_item');
    }

amir5

1 year ago

Level 7

@tykus Query:

select
  *
from
  (
    with recursive `laravel_cte` as (
      (
        select
          `categories`.*,
          1 as `depth`,
          cast(`id` as char(65535)) as `path`
        from
          `categories`
        where
          `id` in (6, 7, 8)
      )
      union all
      (
        select
          `categories`.*,
          `depth` + 1 as `depth`,
          concat(`path`, '.', `categories`.`id`)
        from
          `categories`
          inner join `laravel_cte` on `laravel_cte`.`id` = `categories`.`parent_id`
      )
    )
    select
      `files`.*,
      (
        select
          group_concat(`path` separator '..')
        from
          `laravel_cte`
      ) as `laravel_paths`,
      `category_item`.`category_id` as `pivot_category_id`,
      `category_item`.`categoriable_id` as `pivot_categoriable_id`,
      row_number() over (
        partition by `category_item`.`category_id`
        order by
          `created_at` desc
      ) as `laravel_row`
    from
      `files`
      inner join `category_item` on `files`.`id` = `category_item`.`categoriable_id`
    where
      `category_item`.`categoriable_type` = 'App\Models\File'
      and `program_id` = 1
      and `category_id` in (
        select
          `id`
        from
          `laravel_cte`
        where
          `program_id` = 1
        order by
          `position` asc
      )
  ) as `laravel_table`
where
  `laravel_row` <= 1
order by
  `laravel_row`

limited by 1

Mega_Aleksandar

1 year ago

Level 18

Maybe try with a plain parent/child function on the model - belongsTo and hasMany. It should return you all the subcategories ($parent_id == $given_category) and the $given_category itself.

kwakuoseikwakye

1 year ago

Level 1

Create a relationship using hasMany() to handle parent and child relationships and add a relationship for files using hasMany(). You should have something like this.

 public function children()
 {
     return $this->hasMany(Category::class, 'parent_id');
 }

 public function files()
 {
     return $this->hasMany(File::class);
 }

 public function allChildren()
 {
     return $this->children()->with('allChildren', 'files');
 }

public function allFiles()
{
     return $this->files()->orWhereHas('category', function($query) {
         $query->whereIn('id', $this->getAllChildrenIds());
     });
 }

private function getAllChildrenIds()
 {
     $ids = collect();
     
     $this->allChildren->each(function ($child) use (&$ids) {
         $ids->push($child->id);
         if ($child->allChildren->count() > 0) {
             $ids = $ids->concat($child->getAllChildrenIds());
         }
     });

     return $ids->unique();
 }

Then in your controller you should eager load and have something like this


class CategoryController extends Controller
{
    public function index($categoryId)
    {
       $category = Category::with(['files', 'allChildren.files'])
            ->findOrFail($categoryId);

        return new CategoryResource($category);
    }

and in your resource you should have something like this


  public function toArray($request)
    {
       return [
            'id' => $this->id,
            'name' => $this->name,
            'files' => $this->whenLoaded('files'),
            'sub_categories' => self::collection($this->whenLoaded('allChildren')),
      ];
   }

1 like

Please or to participate in this conversation.