How to count relationships via relation for multi-table model in Laravel?

Question

Laravel

There is a Catalog, which can contain a Brand or a Section,
for the Brand and the Section you need to create a single Product model,
but the name of the Product table depends on the field of the Catalog.

Then, when displaying the Catalog, it is required to display the number of goods in it, how to do this correctly?

App\Catalog.php

<?php

namespace App;

use Illuminate\Database\Eloquent\Model;

class Catalog extends Model
{
    protected $fillable = [
        'name', 'title', 'uri', 'visible', 'parent_id', 'image_path'
    ];

    public $timestamps = false;

    public function subCatalogs(){
        return $this->hasMany('App\Catalog', 'parent_id');
    }

    public function parent(){
        return $this->belongsTo('App\Catalog', 'parent_id');
    }

    public function brands(){
        return $this->belongsToMany('App\Brand');
    }

    public function sections(){
        return $this->belongsToMany('App\Section');
    }

    public function countProduct(){
        //
    }

}

App\Brand.php

<?php

namespace App;

use Illuminate\Database\Eloquent\Model;

use App\Traits\ProductsCount;

class Brand extends Model
{
    use ProductsCount;

    public $timestamps = false;

    public function catalog()
    {
        return $this->belongsToMany('App\Catalog');
    }
}

App\Section.php

<?php

namespace App;

use Illuminate\Database\Eloquent\Model;

use App\Traits\ProductsCount;

class Section extends Model
{
    use ProductsCount;

    public $timestamps = false;

    public function catalog()
    {
        return $this->belongsToMany('App\Catalog');
    }
}

App\Traits\ProductsCount.php

<?php

namespace App\Traits;

trait ProductsCount
{
    public function products(){
        return $this->belongsTo('App\Product');
    }
}

App\Product.php

<?php

namespace App;

use Illuminate\Database\Eloquent\Model;

class Product extends Model {
    public function __construct($product_table){
        parent::__construct();

        $this->table = $product_table;
    }
    public function brand(){
        return $this->belongsTo('App\Brand');
    }

    public function section(){
        return $this->belongsTo('App\Section');
    }

}

App\Http\Controllers\CatalogController.php

<?php

namespace App\Http\Controllers;

use App\Catalog;
use App\Brand;
use Illuminate\Http\Request;
use Illuminate\Support\Facades\Cache;

class CatalogController extends Controller
{
    /**
     * Create a new controller instance.
     *
     * @return void
     */
    public function __construct()
    {
        $this->middleware('web');
    }

    public function showCatalog(Request $request){
        $uri = trim($request->getPathInfo(),"/");

        $catalog = Cache::rememberForever('catalog.' . $uri, function () use ($uri){
            $catalog = Catalog::with(['subCatalogs.brands','parent.parent','brands', 'sections','subCatalogs' => function ($query) {
                $query->where('visible', 1);
            }])->where('uri', $uri)
               ->where('visible', 1)
               ->first();

            if ($catalog === null) abort(404);

            return $catalog->toArray();

        });

        $catalog = Catalog::with(['subCatalogs.brands','parent.parent','brands', 'sections','subCatalogs' => function ($query) {
            $query->where('visible', 1);
        }])->where('uri', $uri)
            ->where('visible', 1)
            ->first();

        dd($catalog->brands()->withCount('products')->get());

        $data['catalog'] = $catalog;

        return view('pages.catalogs', $data);

    }

}

Answer 1

If you are requesting data that is not going to be modified, but only displayed, and even more so if you have a rather complex query, then do not use models - create a query using the DB facade.
DB::table('some_table')->select(...)->join(...)->where() and so on...