CODEWITHSUNDEEP
phpsqllaraveleloquent
Duncan Lukkenaer
Duncan Lukkenaer

Reputation: 13914

Count relations that have a relation in Laravel

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:

  1. All categories
  2. The amount of questions per category
  3. 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?

Upvotes: 0

Views: 302

Answers (2)

Duncan Lukkenaer
Duncan Lukkenaer

Reputation: 13914

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.

Upvotes: 1

rkj
rkj

Reputation: 8287

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

Upvotes: 1

Related Questions