php laravel用的多不,php-使用多个Laravel范围进行关系过滤时出现...

我正在使用Laravel 5.6,并且正在尝试过滤User模型中的关系.用户可以参加课程,这些课程具有要点.

用户可以通过参加课程获得这些积分.这是BelongsToMany关系.

我尝试在此用户模型中创建一个范围,该范围仅包括几年内参加的课程.

/**

* Retrieves the courses which the user has attended

*/

public function attendedCourses()

{

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

}

/**

* Searches the user model

*

* @param \Illuminate\Database\Eloquent\Builder $builder

* @param array $years

*

* @return \Illuminate\Database\Eloquent\Builder

*/

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]);

}

在我的课程模型中,我有一个范围可以过滤该课程所在的年份.

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

{

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

}

希望有了这个有人参与的课程年度范围,我希望我可以使用课程模型上的其他范围,通过汇总课程点数来为每个用户计算点数.

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

{

$categoryIsExternal = function($query) use ($flag) {

$query->external($flag);

};

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

}

在我的CourseCategory模式中,范围如下所示：

/**

* Scope a query to only include external categories.

*

* @param \Illuminate\Database\Eloquent\Builder $query

*

* @param bool $flag

*

* @return \Illuminate\Database\Eloquent\Builder

*/

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

{

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

}

为了计算这个,我尝试做这样的事情.

# Retrieve all the active users

$users = User::all()->attendedCoursesInYears($years);

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

# Calculate the points

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

但是,这返回了所有课程的总和.

如我所见,使用范围是这里的唯一选择.我想使用这样的访问器从这些值创建自定义属性.这样,我可以轻松地对计算得出的值进行排序.

/**

* The users internal course points

*

* @param array $years The years to look for attended courses

*

* @return float

*/

public function getInternalPointsAttribute() : float

{

return $this->attendedCourses()->external(false)->sum('points');

}

唯一的问题是年份过滤器.我希望我可以像第一个示例一样在调用访问器之前过滤User集合.

我在这里做错了什么？

我目前正在使用此解决方法.这似乎很糟糕,因为我要重复很多代码.

/**

* @param \Illuminate\Database\Eloquent\Builder $builder

*

* @param array $years

*

* @return \Illuminate\Database\Eloquent\Builder|\Illuminate\Database\Query\Builder

*/

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

$internalPoints = 'SUM(courses.points_internal)';

$externalPoints = 'SUM(courses.points_external)';

# Select the points

$builder->selectRaw('COALESCE(' . $internalPoints . ', 0) as internal_points');

$builder->selectRaw('COALESCE(' . $externalPoints . ', 0) as external_points');

$builder->selectRaw('COALESCE(' . $internalPoints . ' + ' . $externalPoints . ', 0) as total_points');

# Sum up the course points

return $builder;

}

我的数据库结构的迁移可以在这里找到.

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');

$table->boolean('mijnafas');

});

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');

});

我注意到,仅在调用$test-> attendedCourses时,它会检索已过滤的集合.问题是我无法在此范围内应用.

问题

>为什么不对过滤后的集合求和？

>如何使它能够相应地过滤此集合？