Yii2: select last record from a hasMany relation

Question

my knowledge on mysql is very basic and now im facing a "complex" (for me) query in which im stuck so thank you in advance if someone could give me some light on this.

I have three tables:

Orders

id | name | comments | ...

OrderLines

id | name | sDate | eDate | comments | ...

OrderLinesStats

id | lineID | date | status | ...

Every day OrderLinesStats is updated via a cron job and gets a new record with actual date, status and other fields so the highest id is the actual data.

Im trying to get that last stats row with a relation in yii2 as follows:

in OrdersLines model:

public function getLastOrdersLinesStats()
{
    return $this->hasMany(OrdersLinesStats::className(), ['lineID' => 'id'])
        ->orderBy(['id'=>SORT_DESC])
        ->groupBy('lineID');
}

OrdersModel:

public function getOrdersLines()
    {
        return $this
            ->hasMany(OrdersLines::className(), ['orderID' => 'id'])
            ->orderBy(['typeID' => SORT_ASC, 'name' => SORT_ASC])
            ->with(['lastOrdersLinesStats']);
    }

But when I debug the query looks like this:

SELECT * FROM `ordersLinesStats` WHERE `lineID` IN (1873, 1872, 1884, 1883, 1870, 1874, 1876, 1880, 1871, 1877, 1881, 1882, 1885, 1886, 1869, 1875, 1878) GROUP BY `lineID` ORDER BY `id` DESC

and doesnt give me the last stats record for each line... in fact, it gives me the oldest one. Seems that im missing something but i cant find it.

Thanks again

Answer 1

All you need to do is change the getLastOrdersLinesStats() to be as follows:

public function getLastOrdersLinesStats()
{
    return $this->hasMany(OrdersLinesStats::className(), ['lineID' => 'id'])
        ->orderBy(['id'=>SORT_DESC])
        ->one();
}

This basically returns the last OrderLinesStats row that you want for each Order

You can access this as follows:

if you have an object called myOrder for example then you can access the row you want as myOrder->lastOrderLinesStats

Answer 2

In OrdersModel add getLastOrderLineStat() method that uses via() junction:

public function getLastOrderLineStat()
{
    return $this->hasOne(OrdersLinesStats::className(), ['lineID' => 'id'])
        ->orderBy(['id'=>SORT_DESC])
        ->groupBy('lineID')
        ->via('ordersLines');
}

If $model is an OrdersModel instance, you obtain the last stat row using:

$model->lastOrderLineStat