Whew.

That title is a lot to take in. Let's break it down, cause this is actually a simple concept.

Example Scenario

Let's imagine we have 2 Eloquent models - a Question and an Answer. Those models are linked by a simple BelongsTo and HasMany relationship (a Question->hasMany(Answer) and an Answer->belongsTo(Question)).

Now let's imagine our business logic requires that someone can set a maximum number of answers permitted for each question. Ok, so our Question model will get an attribute called max_answers. If a particular Question has a count of Answers that matches the integer stored in the max_answers attribute, we will consider that Question "full". Perhaps in our imaginary app, we would only reveal answers to "full" questions and so we need to account for this in our API responses for the /answers endpoint.

There are other scenarios where this could prove useful: polls with special logic around a user-configurable number of votes, an ecommerce product that requires a user-configurable number of orders before it goes into production, etc. The key here is "user-configurable". At the very least, this exercise will help you explore some advanced Eloquent concepts.

Here's where the title of this article comes in...

Rephrasing the question

What if you wanted to find a list of Answers that belong to a "full" Question?

You may know, from the Laravel docs, that we can query for records based on the existence of a relation and include a count in that query. It looks like:

Model::query()->has('relation', '=', :integer:)->...;

Or, if you need to specify a constraint on the relation:

Model::query()->whereHas(
    'relation',
    function(Builder $relationQuery) {
        $relationQuery->where('foo', 'bar');
        ...
    }
    '=',
    :integer:
)->...;

Using our Question and Answer example, we could find a list of questions that have exactly 5 answers like so:

Question::query()->has('answers', '=', 5)->get();

Don't forget the operator (= above) could be any database compliant operator (ex. <, >, <>, etc).

However, all of these examples use a hard-coded count value (:integer:).

How do we do this with a count value that is stored in the database so that we can look for "full" Questions?

Unlocking additional functionality

It turns out it's possible to leverage the has() or whereHas() methods to accomplish this. At first, this will seem impossible because the Laravel has() method specifies the third parameter must be of integer type via docBlocks. Nonetheless, the following does work, but it's important to wrap our special count in a Query\Expression, otherwise Eloquent will use a literal string in the final query to the database (like 'questions.max_answers' vs. questions.max_answers), which would cause a useless integer to string comparison by the database engine.

Let's first describe the query for a list of "full" Questions directly, since that's conceptually easier to grasp.

Question::query()
    ->has('answers', '=', new \Illuminate\Database\Query\Expression('questions.max_answers'))
    ->get();
// or, using the DB Facade...
Question::query()
    ->has('answers', '=', \Illuminate\Support\Facades\DB::raw('questions.max_answers'))
    ->get();

Side note: you can use (new Question)->qualifyColumn('max_answers') vs. 'questions.max_answers' if you want Laravel to qualify the columns for you. You will see a version of this shortly, just using an existing query Builder instance instead of a new Model instance...

Technically, one could perform a relation load on the Questions there to resolve the Answers to our original question (Question::query()->with('answers')->has(...)->get()), but the point of this article is to show you how to accomplish the Eloquent setup by looking up the Answers directly.

Final Solution

Ok. We're ready to take this one step further. Let's find Answers that belong to a "full" Question.

The final solution conceptually uses a combination of what we discussed above:

Answer::query()
    ->whereHas(
        'question',
        fn (Builder $whereQuestion) => $whereQuestion
            ->has('answers', '=', $whereQuestion->qualifyColumn('max_answers'))
    )
    ->get();

As you can see, it's possible to use the whereHas() method to easily say "give me answers that belong to a 'full' question as saved in the db."

Hint: under the hood, the whereHas() method on the \Illuminate\Database\Eloquent\Concerns\QueriesRelationships trait is nothing more than an alias to the has() method. The whereHas() definition simply reorders the parameters so that the sub query Closure is in the second slot.

Bonus Round

Want to see two other ways of doing this? See if you can figure out what's going on and how the database may handle each query (use of indexes, etc).

Answer::query()
    ->whereIn('question_id', fn($questionIdQuery) => $questionIdQuery
        ->select('id')
        ->from('questions')
        ->where('questions.max_answers', '=', fn($subQuery) => $subQuery
            ->selectRaw('count(*)')
            ->from('answers')
            ->whereColumn('questions.id', 'answers.question_id')
        )
    )
    ...

The query above is essentially an alternate version of a WHERE EXISTS (...) query and will perform similarly due to foreign key indexes.

Answer::query()
    ->whereHas('question', fn($query) => $query
	    ->withCount('answers')
	    ->groupBy('id')
	    ->havingRaw('answers_count = questions.max_answers')
    )
    ...

Just in case you want to confuse someone by using a HAVING clause... 😅