CODEWITHSUNDEEP
phpmysqlyii2yii2-advanced-app
farrusete
farrusete

Reputation: 435

Yii2: select last record from a hasMany relation

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

Upvotes: 4

Views: 5619

Answers (3)

CyberPunkCodes
CyberPunkCodes

Reputation: 3777

I am just answering this to be thorough and hopefully help other's who stumble upon this page.

I recommend always including both, hasOne and hasMany. This way, you can pop the top record, or retrieve all of them.

/**
 * @return \yii\db\ActiveQuery
 */
public function getUserPlan()
{
    return $this->hasOne(UserPlan::className(), ['user_id' => 'id'])
        ->orderBy(['id' => SORT_DESC])
        ->one();
}

/**
 * @return \yii\db\ActiveQuery
 */
public function getUserPlans()
{
    return $this->hasMany(UserPlan::className(), ['user_id' => 'id'])
        ->orderBy(['id' => SORT_DESC])
        ->all();
}

hasMany will return an array of ActiveQuery Objects, where hasOne will return just an ActiveQuery Object by itself.

You use them like so (example in UserController on User model):

$model = $this->findOne($id);

or

$model = User::findOne($id);

or

$model = User::find()->where(['id' => $id])->one();

Then grab the relations like so:

$plan = $model->userPlan

or

$plans = $model->userPlans

For userPlan:

$planId = $plan->id;

Handling userPlans:

foreach($plans as $plan) {
    $plan->id;
}

Upvotes: 1

mrateb
mrateb

Reputation: 2499

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

Upvotes: 6

Fabrizio Caldarelli
Fabrizio Caldarelli

Reputation: 3008

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

Upvotes: 4

Related Questions