Eloquent get user with highest relation table points

user_11774240 0
Viewed 60

I'm trying to get the user with the highest evaluation points based on charisma and persuasion

What i have wrote so far after this i couldn't figure out how to make it work

    $user = User::where('commision_id', $data['commision'])->whereHas('role', function ($q) {
        $q->where('level', 2);
    })->with(['evaluations' => function ($q) {
        
    }]);

The evaluations relation migration

    Schema::create('evaluations', function (Blueprint $table) {
        $table->id();
        $table->boolean('charisma')->default(false); 
        $table->boolean('persuasion')->default(false);
        $table->boolean('politics')->default(false);
        $table->boolean('heat')->default(false);
        $table->string('title');
        $table->integer('hl')->nullable();
        $table->integer('exp')->nullable();
        $table->unsignedBigInteger('user_id');
        $table->timestamps();

        $table->index('user_id');
    });

So basically i have to sum the exp points based on persuasion and the exp points based on charisma then sum both of those sum values to get the highest ranking user.

eloquent laravel php
2 Answers

You can add up those 2 properties on the fly and simply get the one with the highest attribute.

$user = User::where('commision_id', $data['commision'])->whereHas('role', function ($q) {
    $q->where('level', 2);
})->with(['evaluations'])
->get()
->each(function ($u) { 
    $u->score = $u->evaluations->charisma + $u->evaluations->persuasion
});

$topUser = $user->orderBy('score')->first();

In case one user can have many evaluations, you would do the similar thing:

->each(function ($u) { 
     $u->score = null;
     $charisma  = $u->evaluations->pluck('charisma')->sum();
     $persuasion = = $u->evaluations->pluck('persuasion')->sum();
     $u->score += $charisma;
     $u->score += $persuasion ;
 });
user_88141030

What made it work is using filter function to filter the boolean values of Charisma and Persuasion, thanks to N L which pointed me to the right direction, i accepted his answer.

    $user = User::where('commision_id', $data['commision'])->whereHas('role', function ($q) {
        $q->where('level', 2);
    })->with(['evaluations'])
    ->get()
    ->each(function ($u) { 
        $u->score = null;
        
        $charisma  = $u->evaluations->filter(function($value) {
            return $value->charisma == true;
        })->sum('exp');

        $persuasion = $u->evaluations->filter(function($value) {
            return $value->persuasion == true;
        })->sum('exp');
        
        $u->score += $charisma;
        $u->score += $persuasion;
    })->sortByDesc('score')->first();
user_117742400
Related