Yii2 hasmany condition and multiple table

Question

I added some codes of my application below. It shows only product_category name and count of products which belong to this product_category. It works like a charm. But I want to get category name and its products again which only belongs to certain city. Example, I wanna get the count of products which belong to "Wooden" category and situated in Baku. I think that I must do some changes in my model`s getActiveProducts() function. Any help is appreciated. These are my tables:

product

id | name      | offer_category_id
-----------------------------
1  | Khar      |  3
2  | SantaCruz |  2
3  | Furniture |  2
4  | VT        |  1
5  | newFort   |  4

product_category

id | name
--------------
1  | Khar        
2  | Wooden 
3  | Sion       
4  | VT   
5  | newFort

product_adress

id | city     | offer_id
-----------------------------
1  | Baku      |  1
2  | Ismailly  |  2
3  | Absheron  |  5
4  | Paris     |  4
5  | Istanbul  |  3

My controller:

$data['productCategory'] = ProductCategory::find()
->joinWith('products')
->all();

View code:

<?php foreach($data['productCategory'] as $key=>$value):             
<li>
    <span class="m-offer-count"><?= $value->productsCount; ?></span>
</li>
<?php endforeach; ?>

And finally my ProductCategory model:

public function getActiveProducts() {
        $all_products = $this->hasMany(Product::className(),['product_category_id' => 'id'])->select(['id'])->where(['status'=>1])->all();
        return $all_product;
}
public function getProductsCount() {
        return sizeof($this->activeProducts);
}

Answer 1

1. Relation function should return ActiveQuery object:

   /**
    * @return \yii\db\ActiveQuery  
    **/
   public function getProducts() {
       return $all_products = $this->hasMany(Product::className(), ['product_category_id' => 'id']);
   }
   

   And do not add to such functions (that declare relations) any other conditions and statements and you'll be able to use it in such methods as ActiveQuery::link() and others.

2. You can reuse your relation in other functions

   here is the option for filtering results by city

   /**
    * @param City|null $city filter result by city
    * @return \yii\db\ActiveQuery  
    **/
   public function getActiveProducts($city = null) {
       $query = $this->getActiveProducts()
           ->andWhere(['status' => 1]);
   
       if (!empty($city)) {
           $query->joinWith('address', false)
               ->andWhere(['address.city_id' => $city->id]);
       }
   
       return $query;
   }
   

   Assuming that you have City class and address relation in Product class.

   The second parameter in joinWith disables eager loading to avoid unnecessary additional queries to your DB.

3. You shouldn't select full set of related records to get their count, use ActiveQuery::count() function instead:

   /**
    * @param City|null $city get the count for particular city
    * @return integer
    **/
   public function getActiveProductsCount($city = null) {
       return $this->getActiveProducts($city)->count();
   }
   

4. Use $city filter in your view

   <?php foreach($data['productCategory'] as $category):             
   <li>
       <span class="m-offer-count">
           <?= $category->getActivePoductsCount($city); ?>
       </span>
   </li>
   <?php endforeach; ?>
   

p.s. i think that you don't need to make joinWith in your controller (if you are not using it for further query or eager loading), just select all categories:

$data['productCategory'] = ProductCategory::find()->all();

p.p.s. i believe that here is no need to fetch array a $key => $value because you don't use the $key

Hope this helps...