how can i get the last row with a given foreign key in yii2 with relations?

Question

i have 2 tables

tickets:
-id
etc..

comments:
-id
-ticket_id
-datetime
etc...

ticket_id is the ticket's id on which the comment was made.

one ticket can have 0 or more comments in this way

how should i go about creating a relation in my Ticket model, to fetch it's last comment?

part of my Ticket Model:

class Ticket extends ActiveRecord
{
public function getComments(): ActiveQuery
    {
        return $this->hasMany(Comment::class, ['ticket_id' => 'id']);
    }

    public function getHandler(): ActiveQuery
    {
        return $this->hasOne(User::class, ['id' => 'handler_id']);
    }

    public function getLastComment()
    {
        //???
    }
}

Answer 1

To create a relation in your Ticket model to fetch its last comment, you can define a method called getLastComment. This method will return the last comment based on the datetime field. You can use the orderBy method to order the comments by datetime in descending order and then use one() to get the last comment. Here's how you can do it:

class Ticket extends ActiveRecord
{
    public function getComments(): ActiveQuery
    {
        return $this->hasMany(Comment::class, ['ticket_id' => 'id']);
    }

    public function getHandler(): ActiveQuery
    {
        return $this->hasOne(User::class, ['id' => 'handler_id']);
    }

    public function getLastComment()
    {
        return $this->hasOne(Comment::class, ['ticket_id' => 'id'])
                    ->orderBy(['datetime' => SORT_DESC])
                    ->one();
    }
}

1. hasOne(Comment::class, ['ticket_id' => 'id']) defines the relationship to the Comment model.

2. orderBy(['datetime' => SORT_DESC]) sorts the comments by the datetime field in descending order.

3. one() fetches the first result, which is the most recent comment.

Now, when you call $ticket->getLastComment(), it will return the last comment associated with the ticket or null if there are no comments.