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?

@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
– Duncan Lukkenaer, Commented Jun 26, 2018 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
– rkj, Commented Jun 26, 2018 at 11:31

rkj · Accepted Answer · 2018-06-26 11:55:30Z

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, 2018 at 11:55

answered Jun 26, 2018 at 11:34

rkj

8,3172 gold badges30 silver badges35 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

Duncan Lukkenaer Over a year ago

Doesn't answers_count now contain the amount of answers instead of answered questions?

rkj Over a year ago

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 Over a year ago

if you only need number of questions that has answer then remove the answers from withCount

Duncan Lukkenaer Over a year ago

Ah I see. I got confused about that part as I don't need the amount of answers indeed. Thanks a lot!

rkj Over a year ago

glad it helps you

Duncan Lukkenaer · Accepted Answer · 2018-06-26 11:57:39Z

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.

Collectives™ on Stack Overflow

Count relations that have a relation in Laravel

2 Answers 2

5 Comments

Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

5 Comments

Comments

Linked

Related