output category with child categories and posts and pagination

Question

I want to get category with all children subcategories and posts where id = category_id. Posts should be paginated. In category model I have 2 relations.

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

    public function posts() { 
        return $this->hasMany(Post::class, 'category_id'); 
    }

In controller I have this function

    public function getCategoryWithPaginatedPosts($slug, $perPage = null)
    {
        $columns = ['id', 'parent_id', 'title', 'slug', 'description', 'image'];
        $postsColumns = ['category_id','title','text'];

        $result = Category::whereSlug($slug)
            ->select($columns)
            ->with('children')
            ->with(['items' => function ($q) use ($postColumns) { 
                $q->wherePublished(true)
                  ->select($postColumns)
                  ->orderBy('id', 'ASC')
                  ->paginate(); 
            }]) 
            ->first();
     }

Pagination doesn't work. I just see that number of items is equal to $perPage parameter (and I have more items), but I don't see paginator inside dd($result->items)

It works like that, though I believe it is not the best way to do that. So I can do it in few steps. In first step I retrieve all data from DB and convert models to array, because I don't need models on webpage and I suppose it works faster like that. I would use ->toBase() if it could take mutators and relations from the model. Second step I convert array into stdClass, because it is more comfortable in blade to work with object rather than with array. Third step is to paginate items with mypaginate function (manual paginator in AppService Provider).

    public function getCategoryWithPaginatedPosts($slug, $perPage = null)
    {
        $columns = ['id', 'parent_id', 'title', 'slug', 'description', 'image'];
        $postsColumns = ['category_id','title','text'];

        $result = Category::whereSlug($slug)
            ->select($columns)
            ->with('children')
            ->with(['items' => function ($q) use ($postColumns) { 
                $q->wherePublished(true)
                  ->select($postColumns)
                  ->orderBy('id', 'ASC');
            }]) 
            ->first()
            ->toArray();

        $result = Arr::arrayToObject($result);
        $result->items = collect($result->items)->mypaginate($perPage);

        return $result;

     }

Answer 1

you should not use ->first() after ->paginate(), change something like this,

 $result = Category::whereSlug($slug)
        ->select($columns)
        ->with('children')
        ->with(['items' => function ($q) use ($postColumns) { 
            $q->wherePublished(true)
              ->select($postColumns)
              ->orderBy('id', 'ASC')
        }]) 
       ->paginate(20);