lin lin - 4 years ago 412
PHP Question

Yii2 models - active record joinWith + andWhere date selection

I like to select specific related data on two related Yii2

models
. Both models stuck in a
1:n
relation. The relation is working fine!




Order model:


  • @property integer $id



Order model relation define:

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


timeCapture model:


  • @property integer $id

  • @property integer $orderId

  • @property string $date (its
    Datetime
    in MySQL-DB)






I like to get all
orders
with related
timeCaptures
between a specific date. The following statement will give me all
timeCaptures
. It seems like the
andWhere
clauses are not working!

/**
* Build query
*/
$orders = Order::find()
->joinWith('timeCaptures')
->andWhere([
'>=',
'timeCapture.date',
$startDateSearch->format('Y-m-d H:i:s')
])
->andWhere([
'<=',
'timeCapture.date',
$endDateSearch->format('Y-m-d H:i:s')
])->all();


This is raw SQL output of
$orders->createCommand()->getRawSql()
:

SELECT `order`.*
FROM `order`
LEFT JOIN `timeCapture` ON `order`.`id` = `timeCapture`.`orderId`
WHERE (`timeCapture`.`date` >= '2017-02-01 00:00:00')
AND (`timeCapture`.`date` <= '2017-02-28 00:00:00')


Please provide a answer which uses Yii2 active records. Thanks in advance.

Answer Source

If I get you right, this is what you are looking for:

$startDateSearch = new DateTime('2017-02-10');
$endDateSearch   = new DateTime('2017-02-17');
$orders = Order::find()->with([
    'timeCaptures' => function (\yii\db\ActiveQuery $query) use($startDateSearch, $endDateSearch) {
        $query
            ->andWhere([
                '>=',
                'timeCapture.date',
                $startDateSearch->format('Y-m-d H:i:s')
            ])
            ->andWhere([
                '<=',
                'timeCapture.date',
                $endDateSearch->format('Y-m-d H:i:s')
            ]);
    },
])->all();
var_dump($orders);

And sorry for the late answer

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download