Count relations that have a relation in Laravel

Question

Using the Laravel Eloquent ORM system I have created the following models:

/**
 * @property int $id
 */
class Category extends Model
{
    public function questions()
    {
        return $this->hasMany(Question::class);
    }
}

/**
 * @property int $id
 * @property int $category_id
 */
class Question extends Model
{
    public function answers()
    {
        return $this->hasMany(Answer::class);
    }
}

/**
 * @property int $id
 * @property int $question_id
 */
class Answer extends Model {}

Now I am trying to eager load the following values:

All categories
The amount of questions per category
The amount of answered questions per category

I have solved both 1 and 2 using this code:

$categories = Category
    ::withCount('questions')
    ->get();

$vars = ['categories' => $categories];

For the third value I tried something like this (which does not work):

$categories = Category
    ::withCount(['questions', 'questions as answered_questions' => function ($query) {
        $query->select()
            ->from('answers')
            ->whereRaw('answers.question_id = questions.id');
    }])
    ->get();

How can I efficiently calculate the amount of questions having one or more answers?

this may help you https://stackoverflow.com/questions/39633691/laravel-5-3-withcount-nested-relation — rkj, Jun 26 '18 at 11:21
@rkj Thanks for the suggestion, but it does not solve my problem. With the solution given there I could count the *amount of answers* instead of the *amount of questions with answers*. — Duncan Lukkenaer, Jun 26 '18 at 11:28
what do you mean by amount of questions with answers ? you mean amount of questions that has at least one answer ? — rkj, Jun 26 '18 at 11:31

rkj · Accepted Answer · 2018-06-26T11:55:30.867

1

you can try has

Category Model

class Category extends Model
{
    public function questions()
    {
        return $this->hasMany(Question::class);
    }

    function answers()
    {
      return $this->hasManyThrough(Answer::class, Question::class);
    }
}

Fetch data

$categories = Category::withCount('questions')->has('answers')->get();

foreach($categories as $category){
  $category->name." - ". $category->questions_count;
}

Here questions_count is total question that has at least one answer for that category

edited Jun 26 '18 at 11:55

answered Jun 26 '18 at 11:34

rkj

8,067
2
27
33

Doesn't `answers_count` now contain the *amount of answers* instead of *answered questions*? – Duncan Lukkenaer Jun 26 '18 at 11:48
`questions_count` is the number of question for that category which has at least one answered and `answers_count` is the total number of answered for all questions for that category – rkj Jun 26 '18 at 11:52
if you only need number of questions that has answer then remove the `answers` from `withCount` – rkj Jun 26 '18 at 11:53
Ah I see. I got confused about that part as I don't need the amount of answers indeed. Thanks a lot! – Duncan Lukkenaer Jun 26 '18 at 11:56
glad it helps you – rkj Jun 26 '18 at 11:57

score 1 · Answer 2 · answered Jun 26 '18 at 11:57

With the help of @rkj I found out about the has function. With that I was able to create the following solution:

$categories = Category
    ::withCount(['questions', 'questions as answered_questions' => function ($query) {
        $query->has('answers');
    }])
    ->get();

Now the $category->questions_count and $category->answered_questions are available to me, which is exactly what I need.

Count relations that have a relation in Laravel

2 Answers2