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.

Have you tried to run this query in MySQL and see if any results are returned? Because it looks fine. — Bizley, Feb 05 '17 at 19:55
I believe that your piece of code is perfectly correct, maybe you should re-check your fetching data in case you missed something... — Yerke, Feb 05 '17 at 19:58
@Bizley yea, I tried it. It does what it looks like. It collects all `orders` with related `timeCaptures` - `date <> $dateSearch`. Maybe my example is a bit confusing and not right descriped -> because of the use of `JOINS` is not the right approach. What I need is all `orders` + related `timeCaptures` with `date <> $dateSearch`. Like: `Order::find->with('timeCaptures')->all();`. But this collect all related `timeCaptures` instead of only that ones with `date <> $searchDate`. Sorry if my question is a bit confusing. — lin, Feb 05 '17 at 20:59

score 4 · Accepted Answer · answered Feb 06 '17 at 18:11

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

score 1 · Answer 2 · answered Feb 06 '17 at 00:40

Have you tried this?

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

The idea is that with joinWith you are creating a join that will allow you to filter on both tables, that is exactly what you do. But you can also use eager loading http://www.yiiframework.com/doc-2.0/guide-db-active-record.html#lazy-eager-loading to get the related models in the same query. I believe this might actually create the exact query you are looking for. It should create

SELECT `order`.*, `timeCapture`.*
    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')

In the same time making the proper relation between the retired orders and timeCapture records so you should be able to access them with

$order->timeCaptures

Thanks for your response. Unfortuanaly this does not work. The query still looks like `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')`. Also `$order->timeCaptures` includes items (all items) which are out of the `date` range. You have any other advice? — lin, Feb 06 '17 at 10:44

Yii2 models - active record joinWith + andWhere date selection

2 Answers2