Selecting calculated attributes by query in Laravel

Question

I'm using Laravel 5.6 and I'm trying to use calculated attributes in my User model using the query builder.

A user can attend courses, which has points bound to them. Users can earn these points by attending the courses. This is a BelongsToMany relationship.

My database structure looks like this:

Schema::create('course_attendees', function(Blueprint $table)
{
    $table->integer('id', true);
    $table->integer('user_id')->index('course_attendees_users_id_fk');
    $table->integer('course_id')->index('course_attendees_courses_id_fk');
});

Schema::create('courses', function(Blueprint $table)
{
    $table->integer('id', true);
    $table->string('title');
    $table->string('subject');
    $table->string('presenter');
    $table->date('start_date')->nullable()->comment('Set to not null later');
    $table->date('end_date')->nullable();
    $table->decimal('points', 4)->nullable();
    $table->string('location');
    $table->timestamps();
});

Schema::create('users', function(Blueprint $table)
{
    $table->integer('id', true);
    $table->string('first_name')->nullable();
    $table->string('last_name')->nullable();
    $table->timestamps();
});

Schema::table('course_attendees', function(Blueprint $table)
{
    $table->foreign('course_id', 'course_attendees_courses_id_fk')->references('id')->on('courses')->onUpdate('RESTRICT')->onDelete('RESTRICT');
    $table->foreign('user_id', 'course_attendees_users_id_fk')->references('id')->on('users')->onUpdate('RESTRICT')->onDelete('RESTRICT');
});

It is also possible to show the users points in a specific period of time. Such as the current year.

I know I'm able to do this with mutators, this however is not the option for me, because I can't order them as easily.

My current workaround is using a a local scope on my model like this:

public function scopeWithPoints(Builder $builder, array $years = [])
{
    # Join all columns
    $builder->join('user_roles', 'users.role_id', '=', 'user_roles.id')
            ->leftJoin('course_attendees', 'users.id', '=', 'course_attendees.user_id');

    # Join the course table for the years
    $builder->leftJoin('courses', function(JoinClause $join) use ($years) {
        # Join the courses table with year filters
        $join->on('course_attendees.course_id', '=', 'courses.id');

        # Apply the filters if available
        !empty($years) and $join->whereIn(DB::raw('YEAR(courses.end_date)'), $years);
    });

    # Select the columns
    $builder->select('users.*')->groupBy('users.id');

    # Sums
    $points = 'SUM(courses.points)';

    # Select the points
    $builder->selectRaw('COALESCE(' . $points. ', 0) as points');

    # Sum up the course points
    return $builder;
}

I'm using this code like this:

$users = User:all()->withPoints();
$users->paginate();
//...

$test = $users->find(123)->points;

It feels like I'm repeating alot of code, since I also have these methods in my User modal.

/**
 * Retrieves the courses which the user has attended
 *
 * @param array $years
 *
 * @return \Illuminate\Database\Eloquent\Relations\BelongsToMany
 */
public function attendedCourses(array $years = [])
{
    $courses = $this->belongsToMany(Course::class, 'course_attendees');

    # Filter the years
    if (empty($years)) {
        return $courses;
    }

    return $courses->years($years);
}

/**
 * The users course points
 *
 * @param bool  $internal Whether to retrieve internal or external course points
 * @param array $years    The years to look for attended courses
 *
 * @return float
 */
public function points(bool $internal, array $years = []) : float
{
    # Retrieve the courses
    $courses = $this->attendedCourses($years)->external(!$internal);

    # Sum points
    return $courses->sum('points');
}

Can this be done in a more efficient way using the query builder?

Answer 1

You can use withCount():

public function scopeWithPoints(Builder $builder, array $years = [])
{
    return $builder->withCount([
        'attendedCourses as points' => function($query) use($years) {
            $query->select(DB::raw('COALESCE(SUM(courses.points), 0)'));

            if(!empty($years)) {
                $query->whereIn(DB::raw('YEAR(courses.end_date)'), $years);
            }
        }
    ]);
}