Yii2 models - active record joinWith + andWhere date selection

Question

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.

Recommended for you: Get network issues from WhatsUp Gold. Not end users.

Answer 1

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

Yii2 models - active record joinWith + andWhere date selection

Sign up