Eloquent whereHas Relationship with constraint on particular related Entity

Consider having two models User, and Book the last one has a status column that can obtain different string values active, inactive, deleted, so the user can have multiple books and the book belongs to the user.

how could I get only users that have their last book status = 'inactive'?

The SQL Query for the behavior is given below:

SELECT
    *
FROM
    `users`
WHERE EXISTS
    (
    SELECT
        *
    FROM
        `books`
    WHERE
        `books`.`user_id` = `users`.`id` AND `books`.`status` = 'inactive' AND `books`.`id` =(
        SELECT
            nested.`id`
        FROM
            `books` AS nested
        WHERE
            nested.`user_id` = `users`.`id`
        ORDER BY
            nested.`created_at` DESC
        LIMIT 1
    )
)

I'm using Laravel 5.6

from Newest questions tagged laravel-5 - Stack Overflow https://ift.tt/2QK54d2
via IFTTT