Count Filters

albertchristanto · Jul 19, 2016 · 46a1394 · 46a1394
1 parent 6d37899
commit 46a1394
Show file tree

Hide file tree

Showing 14 changed files with 2,139 additions and 482 deletions.
diff --git a/.idea/sqldialects.xml b/.idea/sqldialects.xml
diff --git a/.idea/workspace.xml b/.idea/workspace.xml
diff --git a/app/Http/Controllers/BackendController.php b/app/Http/Controllers/BackendController.php
@@ -6,7 +6,7 @@
 use App\Models\Category;
 use App\Models\Order;
 use App\Models\Product;
-use App\Models\Brands;
+use App\Models\Brand;
 use Illuminate\Http\Request;
 use Auth, View, Gate, DB;
 use Validator, Input, Redirect;
@@ -116,7 +116,7 @@ public function productsEdit()
         $edit->add('slug', 'Slug', 'text')->rule('required|min:3');
         $edit->add('name', 'Name', 'text')->rule('required|min:3');
         $edit->add('description', 'Description', 'redactor');
-        $edit->add('brand_id', 'Brand', 'select')->options(Brands::lists("brand", "brand_id")->all());
+        $edit->add('brand_id', 'Brand', 'select')->options(Brand::lists("brand", "brand_id")->all());
         $edit->add('cat_id', 'Category', 'select')->options(Category::lists("cat", "cat_id")->all());
         $edit->add('size.size', 'Size', 'tags');
         $edit->add('color.color', 'Color', 'tags');

diff --git a/app/Models/Brands.php → app/Models/Brand.php b/app/Models/Brands.php → app/Models/Brand.php
@@ -4,14 +4,14 @@
 
 use Illuminate\Database\Eloquent\Model;
 
-class Brands extends Model
+class Brand extends Model
 {
     /**
      * The database table used by the model.
      *
      * @var string
      */
-    protected $table = 'brand';
+    protected $table = 'brands';
 
     /**
      * The attributes that are mass assignable.
@@ -37,4 +37,15 @@ public function product()
     {
         return $this->belongsTo('App\Models\Product','brand_id', 'brand_id');
     }
+
+    /**
+     * Count brands
+     * @return mixed
+     */
+    public function brandCount()
+    {
+        return $this->product()
+            ->selectRaw('brand_id, count(*) as aggregate')
+            ->groupBy('brand_id');
+    }
 }
diff --git a/app/Models/Color.php b/app/Models/Color.php
@@ -33,4 +33,15 @@ class Color extends Model
      */
 
     public $timestamps = false;
+
+    /**
+     * count colors
+     * @return mixed
+     */
+    public function colorCount()
+    {
+        return $this->hasOne('App\Models\Colors_products')
+            ->selectRaw('color_id, count(*) as aggregate')
+            ->groupBy('color_id');
+    }
 }
diff --git a/app/Models/Colors_products.php b/app/Models/Colors_products.php
@@ -32,6 +32,9 @@ class Colors_products extends Model
 
     public $timestamps = false;
 
+    /**
+     * @return \Illuminate\Database\Eloquent\Relations\BelongsTo
+     */
     public function product()
     {
         return $this->belongsTo('App\Models\Product');

diff --git a/app/Models/Product.php b/app/Models/Product.php
@@ -47,7 +47,7 @@ class Product extends Model
      */
     public function brands()
     {
-        return $this->hasOne('App\Models\Brands', 'brand_id', 'brand_id');
+        return $this->hasOne('App\Models\Brand', 'brand_id', 'brand_id');
     }
 
     public function category()
@@ -111,4 +111,16 @@ public function scopeItemProperty($query, $id)
     {
         return $query->with('category', 'size', 'color')->findOrFail($id);
     }
+
+    /**
+     * Get parent items for colors and sizes
+     * @param $query
+     * @param $parent
+     * @return mixed
+     */
+    public function scopeGetParents($query, $parent)
+    {
+        return $query->where(['parent_id' => $parent])
+            ->lists('product_id');
+    }
 }
diff --git a/app/Models/Size.php b/app/Models/Size.php
@@ -32,4 +32,15 @@ class Size extends Model
      */
 
     public $timestamps = false;
+
+    /**
+     * count sizes
+     * @return mixed
+     */
+    public function sizeCount()
+    {
+        return $this->hasOne('App\Models\Products_sizes')
+            ->selectRaw('size_id, count(*) as aggregate')
+            ->groupBy('size_id');
+    }
 }
diff --git a/app/Repositories/ProductRepository.php b/app/Repositories/ProductRepository.php
@@ -2,18 +2,20 @@
 
 namespace App\Repositories;
 
-use App\Models\Brands;
+use App\Models\Brand;
 use App\Models\Category;
+use App\Models\Color;
 use App\Models\Product;
+use App\Models\Size;
 use App\Repositories\ShareRepository as Share;
-use Request,DB;
+use Request, DB;
 
 class ProductRepository
 {
 
     /**
      *
-     * Product Class with custom methods for products.
+     * Product repository Class with custom methods for products.
      * Just move logic outside from Eloquent model.
      *
      * @package ecommerce-cms
@@ -23,34 +25,24 @@ class ProductRepository
      */
 
     /**
-     * @param $parent
+     * Get data and count items for filters page.
      * @return mixed
      */
     public static function getAll($parent)
     {
-        $sqla = '(SELECT count(products.brand_id) as count
-                            FROM products
-                            WHERE products.brand_id = brand.brand_id
-                            AND products.parent_id = "' . $parent . '") as brand_cnt';
-        $sqlb = '(SELECT count(color_product.color_id) as count
-                            FROM color_product
-                            LEFT JOIN products
-                            ON products.product_id = color_product.product_id
-                            WHERE color_product.color_id = colors.color_id
-                            AND products.parent_id = "' . $parent . '") as color_cnt';
-        $result = DB::table('brand')
-            ->select(array('*', DB::raw($sqla), DB::raw($sqlb)))
-            ->leftJoin('sizes', 'brand.brand_id', '=', 'sizes.size_id')
-            ->leftJoin('colors', 'brand.brand_id', '=', 'colors.color_id')
-            ->get();
-        $data['brand'] = array();
-        $data['color'] = array();
-        $data['size'] = array();
-        foreach ($result as $val) {
-            $data['brand'][] = $val;
-            $data['color'][] = $val;
-            $data['size'][] = $val;
-        }
+        $parents = Product::GetParents($parent);
+        $data['brand'] = Brand::with(['brandCount' => function ($q) use ($parent) {
+            $q->where('parent_id', $parent);
+        }])->get();
+        $data['brand']->first()->brandCount;
+        $data['color'] = Color::with(['colorCount' => function ($q) use ($parents) {
+            $q->whereIn('product_id', $parents);
+        }])->get();
+        $data['color']->first()->colorCount;
+        $data['size'] = Size::with(['sizeCount' => function ($q) use ($parents) {
+            $q->whereIn('product_id', $parents);
+        }])->get();
+        $data['size']->first()->sizeCount;
         return $data;
     }
 
@@ -60,7 +52,7 @@ public static function getAll($parent)
      */
     public static function getHome()
     {
-        $data['brands'] = Brands::all();
+        $data['brands'] = Brand::all();
         $data['latest'] = Product::latest();
         $data['products'] = Product::Products();
         return $data;
@@ -140,9 +132,9 @@ public static function pagination($parent)
         if (!empty(Request::input('brand'))) {
             $query->whereIn('brand_id', Request::input('brand'));
         };
-        if(Request::input('name')){
+        if (Request::input('name')) {
             $query->orderBy('name', Request::input('name'));
-        }else{
+        } else {
             $query->orderBy('price', Request::input('price'));
         }
         //$query->orderBy('price', Request::input('price'));