Getting the total count of a relationship tables results

Question

Hi I'm trying to get a vote count results back from my database but not sure on the best approach.

Topic model

protected $fillable = [
    'division_id',
    'date',
    'title',
    'description',
    'aye_count',
    'noe_count'
];

public function votes()
{
    return $this->hasMany(Vote::class, 'topic_id', 'division_id');
}

Vote model

protected $fillable = [
    'topic_id',
    'user_id',
    'user_aye_count',
    'user_noe_count'
];

function topic() {
    return $this->belongsTo(Topic::class, 'topic_id', 'division_id');
}

My controller looks like this

public function filterTopicsByTitle(Request $request)
{

    $division_id = $request->get('filterTopicsByTitle');

    $topicResults = Topic::with(['votes'])->where('division_id', $division_id)->first();

    dd($topicResults->votes);

    return view('dashboard', compact('topicResults'));

}

the dump outputs the following

Illuminate\Database\Eloquent\Collection {#1259 ▼
  #items: array:2 [▼
    0 => App\Models\Vote {#1263 ▼
      +timestamps: false
      #fillable: array:4 [▶]
      #connection: "mysql"
      #table: "votes"
      #primaryKey: "id"
      #keyType: "int"
      +incrementing: true
      #with: []
      #withCount: []
      #perPage: 15
      +exists: true
      +wasRecentlyCreated: false
      #attributes: array:5 [▼
        "id" => 1
        "user_id" => 2
        "topic_id" => 965
        "user_aye_count" => 1
        "user_noe_count" => null
      ]
      #original: array:5 [▶]
      #changes: []
      #casts: []
      #classCastCache: []
      #dates: []
      #dateFormat: null
      #appends: []
      #dispatchesEvents: []
      #observables: []
      #relations: []
      #touches: []
      #hidden: []
      #visible: []
      #guarded: array:1 [▶]
    }
    1 => App\Models\Vote {#1265 ▼
      +timestamps: false
      #fillable: array:4 [▶]
      #connection: "mysql"
      #table: "votes"
      #primaryKey: "id"
      #keyType: "int"
      +incrementing: true
      #with: []
      #withCount: []
      #perPage: 15
      +exists: true
      +wasRecentlyCreated: false
      #attributes: array:5 [▶]
      #original: array:5 [▼
        "id" => 5
        "user_id" => 1
        "topic_id" => 965
        "user_aye_count" => null
        "user_noe_count" => 1
      ]
      #changes: []
      #casts: []
      #classCastCache: []
      #dates: []
      #dateFormat: null
      #appends: []
      #dispatchesEvents: []
      #observables: []
      #relations: []
      #touches: []
      #hidden: []
      #visible: []
      #guarded: array:1 [▶]
    }
  ]
}

I basically need to create 2 variables $userAyeCount & $userNoeCount with the totals. The only way I can think to do it is a foreach loop getting the row user_aye_count or user_noe_count and incrementing the variables.

It just doesn't seem the right approach and when there are a significant number or rows I'm guessing it will be much slower. Any assistance would be appreciated.

Answer 1

I believe you need total count of user_aye_count and user_noe_count for a specific topic If that is the case you can customize the withCount method and perform your aggregation to sum up these count for all related records from votes model

 $topicResult = Topic::withCount([
                            'votes as aye_count' => function ($query) {
                                        $query->select(DB::raw("sum(user_aye_count)"));
                            },
                            'votes as noe_count' => function ($query) {
                                        $query->select(DB::raw("sum(user_noe_count)"));
                            },
                            'votes as total' => function ($query) {
                                        $query->select(DB::raw("sum(user_aye_count + user_noe_count)"));
                            }
                    ])->where('division_id', $division_id)
                      ->first();

Laravel 8 has now introduced withSum luxury and I guess you can add multiple withSum clauses in query builder as

$topicResult = Topic::withSum('votes', 'user_aye_count')
                     ->withSum('votes', 'user_noe_count')
                     ->select(['topics.*', DB::raw('votes_sum_user_aye_count  + votes_sum_user_noe_count as total')])
                     ->first();

The result values can be accessed as

$topicResult->votes_sum_user_aye_count;
$topicResult->votes_sum_user_noe_count;
$topicResult->total;