Get records based on a related table property

Hi there!

To simplify things let's say I have the following two tables, with this properties:

| Users | | --------- | | id | | name | | email | | is_active |

| Comments | | ----------- | | id | | user_id | | message | | answer_id | | created_at |

Every user can have many comments and each comment belongs to one user.

What I want is to get a list of users whose last comment have an answer id of 1 or 3. Also, I want to filter the list where users have an active status and email is like xxxx.

To get this I created the following relations and scope:

class User extends Model {

    ...

    public function comments(): HasMany
    {
    	return $this->hasMany('App\Models\Comment');
    }

    public function lastComment(): HasOne
    {
    	return $this->hasOne('App\Models\Comment')->latest();
    }

	...
    
    public function scopeWhereLastAnswerIdIn($query, array $answerIds)
    {
    	return $query->whereHas('lastComment', function ($q) use ($answerIds) {
			$q->whereIn('answer_id', $answerIds);
        });
	}
}

And in controller I call this scope like this:

class ExampleController extends Controller {

    ...

    public function getUsers()
    {
    	return User::whereLastAnswerIdIn([1, 3])
			->where('is_active', true)
            ->where('email', 'LIKE', '%xxxx%')
            ->get();
    }

	...
}

I can't make this work, as generated SQL is not getting the last comment properly and the WhereIn condition applies to all comments. Anything I'm doing wrong? There's a better way to get this?

(edit) This is the generated SQL:

SELECT *
FROM `users`
WHERE EXISTS (
    SELECT *
    FROM `comments`
    WHERE `users`.`id` = `comments`.`user_id`
    and `answer_id` in (1, 3)
)
and `is_active` = 1
and `email` LIKE '%xxxx%';