Eloquent - How to get model records where the count of that model for a parent model matches a column of that parent model instead of a PHP value? Pseudo: select * from answers where the count of answers on the answer's question is equal to the question's max_answers attribute?
Hello everyone,
I've been scratching my head at this one for a while and can't seem to come up with a decent solution. I'm hoping an Eloquent wizard can help with this, albeit edge case, challenge. I have attempted to do some Google + StackOverflow + Laracasts searching, but wasn't able to find something that deals with the specifics of this problem. I even bought & watched Jonathan Reinink's course on Eloquent to see if something might relate. Although there's a ton of useful info in there, I don't believe he provided an example like this.
I'm working with 2 Models as follows:
class Answer extends Model
{
// Just to show important attributes on the model...
protected $casts = [
'id' => 'int',
'question_id' => 'int',
'user_id' => 'int',
...
];
public function question(): BelongsTo
{
return $this->belongsTo(Question::class);
}
}
class Question extends Model
{
// Just to show important attributes on the model...
protected $casts = [
'id' => 'int',
'max_answers' => 'int',
...
];
public function answers(): HasMany
{
return $this->hasMany(Answers::class);
}
}
A straightforward $question->answers() arrangement, with one important caveat:
max_answers attribute which, as you can imagine, determines the maximum number of answers that question is allowed to have.
max_answers attribute is defined by the users of our system. They pay per answer provided to their question, so our users want to specify the maximum # of answers per question to control their budget.max_answers spec can't be hard-coded.answers that belong to questions where the count of answers on the related question is equal to the question's max_answers attribute.max_answers attribute) a "full" question for the sake of discussion.For "normal" situations involving counts of related models and constraining queries by them, one can do something like:
Answer::whereHas('question', fn ($query) => $query->has('answers', 5));
However, that makes the "count constraint" hard-coded (or a reference to something in PHP-land). That won't work for our requirements.
// Answer Model
public function scopeBelongingToFullQuestion(Builder $query): Builder
{
return $query->whereHas('question', fn(Builder $question) => $question
->withCount('answers')
->havingRaw('answers_count >= max_answers')
);
}
This seems to produce the desired results with MySQL, but when running tests via SQLite, it throws an SQL exception stating it needs a GROUP BY to go along with the HAVING. Bugga. If I proceed to add a silly ->groupBy('id') into the query builder, I don't get any results in my tests.
Ok. That's the jist. If anyone can help clarify a good Eloquent statement that will produce the desired results in MySQL & SQLite, I'd be incredibly thankful.
@stevenfox You could simply add a groupBy('id') clause to your query and it should work (if I am not mistaken).
return $query->whereHas('question', fn(Builder $question) => $question
->withCount('answers')
->havingRaw('answers_count >= max_answers')
->groupBy('id') <-- Right here :)
);
Thanks for the reply!
As mentioned at the end of my original post, I had tried that ->groupBy('id') previously but I wasn't getting the anticipated results in my tests. However, because of your answer, I went back and tried that solution again. It turns out it does indeed work, and it was my original test that was flawed. :facepalm:
For kicks, I also attempted another solution and it produces the desired results (it's based on a lesson from Reinink's course). I will include it here in case it helps anyone in the future.
// Answer Model
public function scopeBelongingToFullQuestion(Builder $query): Builder
{
return $query->whereIn('question_id', fn($query) => $query
->select('id')
->from('questions')
->where('questions.max_answers', '<=', fn($subQuery) => $subQuery
->selectRaw('count(*)')
->from('answers')
->whereColumn('questions.id', 'answers.question_id')
)
);
}
The key to making the above work is that Eloquent allows you to pass a closure as the third argument to the ->where() method, and rather than using that as a bound parameter, it'll use it as a comparison column. Thus, we can perform a subSelect style query there and the generated SQL is correct.
Depending on the indexes one has defined on their tables, this may or may not be faster/slower than the
$query->whereHas('relation', fn($query) => $query
->withCount('...')
->havingRaw('...')
->groupBy('id'))
approach. On my particular setup & tables, the ->whereHas() is marginally faster, and the SQL EXPLAIN suggests similar efficiency and because of index usage. Your mileage may vary.
I personally prefer the ease of the ->whereHas() approach in this situation anyway, because you can create a scope on the related model and then use that on the child. For example:
// Question Model
public function scopeWhereHasReachedAnswerLimit(Builder $query): Builder
{
return $query->withCount('answers')
->groupBy('id')
->havingRaw('answers_count >= max_answers');
}
// Answer Model
public function scopeBelongingToFullQuestion(Builder $query): Builder
{
return $query->whereHas('question', fn(Builder $question) => $question
->whereHasReachedAnswerLimit()
);
}
Just be careful of query grouping if you use this approach with additional where clauses, joins, etc.
Beautiful
As mentioned at the end of my original post, I had tried that ->groupBy('id') previously
Missed that one obviously...sorry
Please or to participate in this conversation.