Query Builder adding OR in a loop

Yii 2.0 General Discussions
1

How do you ‘OR’ conditions together in a loop. For example, I have models Property -> PropertyPrices. The search form has bands of prices, from 0 - 1500, 1501 - 3000 etc … A user may tick more than one band, so I want to say price between 0 and 1500 OR price between 1501 and 3000

I was planning to build this as a sub-query and then join that to the Property query but just can’t figure out the correct syntax;




       

       $prices = [

            ['from' => 0, 'to' => 1500],

            ['from' => 1501, 'to' => 3000]

        ];

        

        $subQuery = new \yii\db\Query;

        $subQuery->from('property_prices');

        foreach ($prices as $pp)

        {

            $subQuery->andWhere(['between', $joinField, $pp['from'], $pp['to']]);

            // also tried

           // $subQuery->andWhere(['or', ['between', $joinField, $pp['from'], $pp['to'] ] ]);

        }


        return $subQuery->createCommand()->rawSql;

I’ve also tried




        $subQuery = new \yii\db\Query;

        $subQuery->from('property_prices');

        $or = [];

        foreach ($prices as $pp)

        {

          $or[] = [['between', $joinField, $pp['from'], $pp['to']]];

        }


        $subQuery->andWhere(['or', $or]);

        

        return $subQuery->createCommand()->rawSql;

Any ideas? Thanks …

2

OK, figured this out as follows:-





        $property = Property::find();

        

        $prices = [

            ['from' => 0, 'to' => 1499],

            ['from' => 1500, 'to' => 3000]

        ];

        

        $subQuery = new \yii\db\Query;

        $subQuery->select('property_id')->distinct(true)

                ->from('property_prices');

        foreach ($prices as $pp)

        {

            $subQuery->orWhere(['between', $joinField, $pp['from'], $pp['to']]);

        }


        $property->innerJoin(['pp' => $subQuery], ['properties.id' => new \yii\db\Expression('pp.property_id')]);

        

        return $property->createCommand()->rawSql;