kingmaker_bgp
3 months ago

Eloquent whereHas Relationship with constraint on particular related Entity

Posted 3 months ago by kingmaker_bgp

Hello Web Artisans and Laravel Enthusiasts,

I had a very interesting and challenging question while browsing through Laracasts

What If we need to have a whereHas constraint on a Many mapping (HasMany, BelongToMany) Relation such that "Related Entity is also subjected to a Constraint"?

Example 1

Original Question

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 the last book with status deleted?

class User extends Model {
	public function books() {
		return $this->hasMany(Book::class);
	}
}
class Book extends Model {
	public function user() {
		return $this->belongsTo(User::class);
	}
}

The Intuitive thought (which doesn't work) would be like

return $user->whereHas('books', function (Builder $query) {
	$query->latest()
		->where('status', '=', 'deleted');
});

This gives Users that has atleast one (among many Books) that has status = 'deleted'. But what we expected to achieve is to get Users that has last (created_at) Book with status = 'deleted'

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` = 'deleted' AND `books`.`id` =(
        SELECT
            nested.`id`
        FROM
            `books` AS nested
        WHERE
            nested.`user_id` = `users`.`id`
        ORDER BY
            nested.`created_at` DESC
        LIMIT 1
    )
)

Example 2

Original Link

Question

How to achieve this query in the Eloquent?

Please sign in or create an account to participate in this conversation.