Pfthb

I'm using Laravel 5.6 and I'm trying to filter a relationship inside my User model. 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.

I'm tried creating a scope in this User model that would only include the attended courses in a range of years.

/**
 * Retrieves the courses which the user has attended
 */
public function attendedCourses()

 return $this->belongsToMany(Course::class, 'course_attendees');


/**
 * Searches the user model
 *
 * @param IlluminateDatabaseEloquentBuilder $builder
 * @param array $years
 *
 * @return IlluminateDatabaseEloquentBuilder
 */
public function scopeAttendedCoursesInYears(Builder $builder, array $years)

 # Filter on the years
 $callback = function($q) use ($years) 
 foreach ($years as $year) 
 $q->year($year);
 
 ;

 return $builder->with(['attendedCourses' => $callback]);

In my Course model, I have a scope that filters on the year that course was in.

public function scopeYear(Builder $query, int $year)

 return $query->whereYear('end_date', $year);

With this attendedCoursesInYears scope I hoped I could then calculate the amount of points for each user by summing up the course points, using other scopes on the Course model.

public function scopeExternal(Builder $query, bool $flag = true)

 $categoryIsExternal = function($query) use ($flag) 
 $query->external($flag);
 ;

 return $query->whereHas('category', $categoryIsExternal);

In my CourseCategory modal, the scope looks like this:

/**
 * Scope a query to only include external categories.
 *
 * @param IlluminateDatabaseEloquentBuilder $query
 *
 * @param bool $flag
 *
 * @return IlluminateDatabaseEloquentBuilder
 */
public function scopeExternal(Builder $query, $flag = true)

 return $query->where('type', '=', $flag ? 'Extern' : 'Intern');

For calculating this, I tried doing something like this.

# Retrieve all the active users
$users = User::all()->attendedCoursesInYears($years);
$test = $users->find(123);

# Calculate the points
$test->attendedCourses()->external(false)->sum('points');

This however returned the total sum of all courses.

I noticed when just calling $test->attendedCourses it retrieves the filtered collection. The problem with this is that I can't apply the scopes on this.

Questions

• How does it come that it will not sum the filtered collection?


• How can I make it so that it will filter this collection accordingly?

The problem is that your scopeAttendedCoursesInYears method in your User class is to scope out the users who took the courses in the specific years and not scope out the courses that were taken during the years.

To do what you want, you could add a parameter to your relation to filter out the pivot table instead of your users table:

public function attendedCourses(array $years = null)

 $query = $this->belongsToMany(Course::class, 'course_user');

 if ($years) 
 $query->whereRaw('YEAR(end_date) IN (?)', [
 'years' => implode(',', $years)
 ]);
 

 return $query;

Then you could achieve your result like this:

$user = User::find(123);

$user->attendedCourses($years)->external(false)->sum('points');