Query ActiveRecord for unique foreign key with max timestamp

Yii 2.0 General Discussions
1

I’m trying to prepare a query and I’m having a hard time with it. I need some Yii2 gurus to help please…

Take the following table as an example…


CREATE TABLE `order_revision` (

  `id` int(11) NOT NULL,

  `parent_order_id` int(11) NOT NULL,

  `user_id` int(11) DEFAULT NULL,

  `sub_total` decimal(19,4) NOT NULL DEFAULT '0.0000',

  `tax_total` decimal(19,4) NOT NULL DEFAULT '0.0000',

  `status` smallint(6) NOT NULL DEFAULT '1',

  `created_at` int(11) NOT NULL,

  `updated_at` int(11) DEFAULT NULL

)

I need a query to select all unique ‘parent_order_id’ with the max ‘updated_at’ value. This query would return an array of “OrderRevision” models that have unique 'parent_order_id’s based on the max timestamp of the ‘updated_at’ column.

In other words, each row returned should have an unique ‘parent_order_id’ and be the maximum timestamp of the’updated_at’ column.

The query in raw SQL looks like this…

SELECT max(id), parent_order_id ,max(updated_at) FROM order_revision GROUP BY parent_order_id

2

Something like this?




SELECT main.*

FROM order_revision AS main

WHERE main.id = (

    SELECT sub.id

    FROM order_revision AS sub

    WHERE main.parent_order_id = sub.parent_order_id

    ORDER BY sub.updated_at DESC

    LIMIT 1

);
3

Thank you a million times Softark! This query is exactly what I was looking for. It works perfectly. Do you know how I would put this query together using an ActiveRecord search or find method?

4

Try this.




// Main Query

$query = OrderRevision::find()

    // FROM `order_revision` AS `main`

    ->from(['main' => OrderRevision::tableName()]);


// Sub Query

$subQuery = OrderRevision::find()

    // SELECT `sub`.`id`

    ->select(['sub.id'])

    // FROM `order_revision` AS `sub`

    ->from(['sub' => OrderRevision::tableName()])

    // WHERE `sub`.`parent_order_id` = `main`.`parent_order_id`

    ->where(['sub.parent_order_id' => Expression('main.parent_order_id')])

    // ORDER BY `sub`.`updated_at` DESC

    ->orderBy(['sub.updated_at' => SORT_DESC])

    // LIMIT 1

    ->limit(1);


// Main Query : WHERE `main`.`id` = sub query

$query->where(['=', 'main.id', $subQuery]);

You have to use ‘Expression’ in where clause of the sub query, otherwise ‘main.parent_order_id’ will be treated as a litteral string.

And the following didn’t work for me.




$query->where(['main.id' => $subQuery]);

It produced:




`main`.`id` in (SELECT ...
5

Thank you. I will give this a try. I really appreciate the time you took on this.

6

I see the problem, you were having with the




`main`.`id` in (SELECT ...

. Other than that it works great. I just can’t figure out how to fix it because I don’t have the


Expression()

method. I couldn’t even find anything about it in the PHP API. I’m using PHP 5.4. Any ideas what else I culd use instead of the “Expression” method?

Wthout using the Expression method I get the following SQL which does not work…


SELECT * FROM `order_revision` `main` WHERE `main`.`id` = (SELECT `sub`.`id` FROM `order_revision` `sub` WHERE `sub`.`parent_order_id`='main.parent_order_id' ORDER BY `sub`.`updated_at` DESC LIMIT 1)

if I change one small thing so it looks like this, it works…


SELECT * FROM `order_revision` `main` WHERE `main`.`id` = (SELECT `sub`.`id` FROM `order_revision` `sub` WHERE `sub`.`parent_order_id`= `main`.`parent_order_id` ORDER BY `sub`.`updated_at` DESC LIMIT 1)
7

yii\db\Expression

http://www.yiiframework.com/doc-2.0/yii-db-expression.html

8

Works like a charm! Thank you very much!