CODEWITHSUNDEEP
joinyii2filtering
vvpanchev
vvpanchev

Reputation: 577

Yii2 dynamically add joinWith

i have an online store with dynamic fields (specifications) and filters. All specifications are stored in table e.g

product_specifications:
-id
-product_id
-spec_id
-spec_val_id

Filters are connected with specifications, so when i send filter value i send specification values. For example

$_GET['Filters']['filter_id']['spec_val_id']

When i loop filters, i want to add left joins to product query. Something like that

$joinString = "";
foreach($filters){
$joinString .= "LEFT JOIN product_specifications AS prod_'.filter.' ON .....";
}

I have query to ActiveDataProvider like that:

$prodQuery = Product::find()->joinWith('translation')->joinWith('cats')->[HERE FILTERS JOINS]->where($whereString)->groupBy(['id']);

But if i have 5 filters, i need 5 joins to table product_specifications. Can i add in joinWith an array with all joins or add 5 joins to query chain? To one category my filters are also dynamically, so the page can have 5 or 10 filters, i can't add static number of joinWith('specs').

Thanks in advance.

I'm agree with different desicion, too.

EDIT:

I change query with findBySql like this

$prodQuery = Product::findBySql('
              SELECT `product`.* 
              FROM `product` 
              LEFT JOIN `productLang` ON `product`.`id` = `productLang`.`product_id` 
              LEFT JOIN `product_cat` ON `product`.`id` = `product_cat`.`product_id` 
              LEFT JOIN `page` ON `product_cat`.`page_id` = `page`.`id` 
              LEFT JOIN `page` `parent` ON `page`.`id_in` = `parent`.`id`'
              .$joinString.
              ' WHERE ( '
                    .$whereString.
                    ' AND (`language`=\''.$lang->url.'\')) GROUP BY `product`.`id` ');

And my dataprovider:

$dataProvider = new ActiveDataProvider([
                'query' => $prodQuery,
                'pagination' => [
                    'totalCount' => count($prodQuery->all()),
                    'pageSize' => $pageSize,
                    'route' => Yii::$app->getRequest()->getQueryParam('first_step'),
                ],
                'sort' => [
                    'defaultOrder' => $orderArr,
                ],
            ]);

Now my problem is pagination and sorting. In my test site i have 4 products, when i set $pageSize = 1;, I have 4 pages in pagination, but in every page i have all 4 products.

Where is my mistake?

Upvotes: 0

Views: 615

Answers (1)

ScaisEdge
ScaisEdge

Reputation: 133380

Could be you can use andFilterWhere (if the value is null no where codntion is added otherwise the and Where is added)

$prodQuery = Product::find()->joinWith('translation')->joinWith('cats')->where($whereString)->groupBy(['id']);


$prodQuery->andFilterWhere(['attribute1' => $value1])
          ->andFilterWhere(['attribute2' => $value2])
          ->andFilterWhere(['attribute3' => $value3])
          ->andFilterWhere(['attribute4' => $value4])
          ->andFilterWhere(['attribute5' => $value5])

Upvotes: 0

Related Questions