时间戳

首页 > 解决方案 > 在 Laravel 中按关系搜索和排序

php - 在 Laravel 中按关系搜索和排序

问题描述

我在 Laravel 5.8 中有项目。

我有这个功能：

public function getCourserFromDates(string $dateFrom, string $dateTo)
{
    return $this->model->getCourserFromDates( $dateFrom,  $dateTo)->get();
}

还有我的模型：

class Course extends Model implements Sitemapable
{
    use HasSitemap,
        Seoable,
        SoftDeletes;

    protected $fillable = [
        'course_category_id',
        'basic_course_id',
        'default_currency_id',
        'short_name',
        'color',
        'price',
        'has_negotiable_price',
        'deposit',
        'certificate_title',
        'certificate_description',
        'certificate_number',
        'is_active',
        'created_by',
        'updated_by',
        'deleted_by'
    ];

    protected $dates = [
        'created_at',
        'updated_at',
        'deleted_at'
    ];

    protected $casts = [
        'course_category_id' => 'integer',
        'basic_course_id' => 'integer',
        'default_currency_id' => 'integer',
        'is_active' => 'boolean',
        'price' => 'float',
        'deposit' => 'float',
        'created_by' => 'integer',
        'updated_by' => 'integer',
        'deleted_by' => 'integer'
    ];

    protected $appends = [
        'name'
    ];

    protected $with = [
        'courseDescriptions'
    ];

    public function courseCategory()
    {
        return $this->belongsTo(CourseCategory::class);
    }

    public function basicCourse()
    {
        return $this->belongsTo(self::class);
    }

    public function defaultCurrency()
    {
        return $this->belongsTo(Currency::class);
    }

    public function courseTerms()
    {
        return $this->hasMany(CourseTerm::class);
    }

    public function closestCourseTerms()
    {
        return $this
            ->courseTerms()
            ->where('is_active', true)
            ->whereDate('starting_at', '>', today()->format('Y-m-d'))
            ->whereDate('starting_at', '<=', today()->addDays(config('courses.closest_terms_within_days', 0)))
            ->orderBy('starting_at');
    }

    public function getCourserFromDates(string $dateFrom, string $dateTo)
    {
        return $this
            ->with('courseTerms')
            ->where('is_active', true)
            ->whereHas('courseTerms', function ($query) use ($dateFrom, $dateTo){
                $query->whereBetween('starting_at', [$dateFrom, $dateTo]);
                $query->orderBy('starting_at');
            });
    }

    public function closestConfirmedCourseTerms()
    {
        return $this->closestCourseTerms
            ->where('is_confirmed', true)->all();
    }

    public function closestNotConfirmedCourseTerms()
    {
        return $this->closestCourseTerms
            ->where('is_confirmed', false)->all();
    }

    public function closestCourseTerm()
    {
        return $this
            ->hasOne(CourseTerm::class)
            ->withCount('courseParticipants')
            ->where('is_active', true)
            ->whereDate('starting_at', '>', today()->format('Y-m-d'))
            ->orderBy('starting_at');
    }

    public function courseParticipants()
    {
        return $this->hasManyThrough('App\Models\CourseParticipant','App\Models\CourseTerm');
    }

    public function courseDescriptions()
    {
        return $this->hasMany('App\Models\CourseDescription');
    }

    public static function allForSitemap(): Collection
    {
        return self::where('is_active', true)
            ->select(['id','is_active', 'created_at', 'updated_at'])
            ->get();
    }

    public function hasTranslation($locale = null)
    {
        $courseDescriptions = $this->courseDescriptions ?? $this->courseDescriptions();

        return $courseDescriptions
            ->where('locale', $locale ?: \App::getLocale() )
            ->count() === 1;
    }

    public function translatedDescriptions($locale = null)
    {
        $courseDescriptions = $this->courseDescriptions ?? $this->courseDescriptions();

        if( $this->hasTranslation($locale) )
        {
            return $courseDescriptions->where('locale', $locale ?: \App::getLocale() )->first();
        }
        /*
         * Playin' dirty
         */
        if( $this->hasTranslation('pl') )
        {
            return $courseDescriptions->where('locale', 'pl' )->first();
        }

        throw new \Exception('Requested language not found');
    }

    public function getNameAttribute() {
        $description = $this->courseDescriptions->where('locale', \App::getLocale())->first();

        return $description instanceof CourseDescription
            ? $description->name
            : $this->short_name;
    }

    public function getShortDescriptionAttribute() {
        return data_get($this->courseDescriptions->where('locale', \App::getLocale())->first(), 'short_description', "");
    }

    public function getDescriptionAttribute() {
        return data_get($this->courseDescriptions->where('locale', \App::getLocale())->first(), 'description', "");
    }

    public function getSlugAttribute() {
        return data_get($this->courseDescriptions->where('locale', \App::getLocale())->first(), 'slug', "");
    }

    public function getIsRenewalAttribute() : bool {
        return $this->basicCourse instanceof self;
    }

    public function courseTags()
    {
        return $this->hasMany(CourseTag::class);
    }

    public function tags()
    {
        return $this->belongsToMany(Tag::class)->using(CourseTag::class);
    }

    public function getUrlAttribute() {
        return ! empty($slug = $this->slug)
            ? route('front.course.show', ['slug' => $slug])
            : route('front.course.find', ['id' => $this->id]);
    }

    /**
     * @param Builder $query
     * @return Builder
     */
    public function scopeWhereActiveWithTerms(Builder $query) : Builder
    {
        return $query
            ->where('is_active', true)
            ->whereExists(function ($query) {
                $query->select(DB::raw(1))
                    ->from('course_terms')
                    ->whereRaw("course_id = courses.id and is_active = 1 and starting_at > '" . today()->format('Y-m-d') . "'");
        });
    }

    public function getCourseCategoryUrlAttribute() {
        if(empty($this->courseCategory)) {
            return null;
        }

        if(is_null($this->courseCategory->parent_category_id)) {
            return $this->courseCategory->url;
        }

        return $this->courseCategory->parentCategory->url;
    }

    public function getLocalizedUrlsAttribute() {
        $urls = [];

        foreach(LaravelLocalization::getSupportedLocales() as $locale => $properties) {
            if(! is_null($slug = $this->getSlug($locale))) {
                $urls[$locale] = array_merge($properties, [
                    'url' => LaravelLocalization::getURLFromRouteNameTranslated($locale,  'routes.front.course.show', ['slug' => $slug])
                ]);
            }
        }

        return $urls;
    }

    public function getSlug($locale): string {
        return data_get($this->courseDescriptions->where('locale', $locale)->first(), 'slug', "");
    }

    public function getGtagViewItemDataAttribute(): array
    {
        $viewItemData = [
            'id' => $this->id,
            'name' => $this->name,
            'category' => data_get($this->courseCategory, 'name', '')
        ];

        if(! $this->has_negotiable_price)
        {
            $viewItemData['price'] = $this->price;
        }

        return $viewItemData;
    }
}



class CourseTerm extends Model
{
    use SoftDeletes;

    protected $fillable = [
        'starting_at',
        'days',
        'course_id',
        'max_participants',
        'additional_notes',
        'is_active',
        'is_confirmed',
        'diary_generated_at'
    ];

    protected $dates = [
        'diary_generated_at',
        'created_at',
        'updated_at',
        'deleted_at'
    ];

    protected $casts = [
        'course_id' => 'integer',
        'days' => 'integer',
        'max_participants' => 'integer',
        'is_active' => 'boolean',
        'is_confirmed' => 'boolean',
        'starting_at' => 'date:Y-m-d'
    ];

    protected $appends = [
        'date'
    ];

    public function course()
    {
        return $this->belongsTo('App\Models\Course');
    }

    public function courseParticipants()
    {
        return $this->hasMany('App\Models\CourseParticipant');
    }

    public function freeReservations() : int
    {
        return (int) array_key_exists('course_participants_count', $this->attributes)
            ? $this->max_participants - $this->course_participants_count
            : $this->max_participants - $this->courseParticipants()->count();
    }

    public function getHasFreeReservationsAttribute() : bool {
        return $this->freeReservations() > 0;
    }

    public function getReservationsProgressInPercentAttribute() : int {
        if($this->max_participants == 0) {
            return 0;
        }

        return (int) round(($this->max_participants - $this->freeReservations())/ $this->max_participants * 100);
    }

    public function getUnifiedDaysAttribute() : int {
        return max($this->days - 1, 0);
    }

    public function getEndingAtAttribute() : Carbon {
        return Carbon::parse($this->starting_at)->addDays($this->unified_days);
    }

    public function getDateAttribute($value) : string
    {
        if($this->unified_days == 0) {
            return $this->starting_at->format("d-m-Y");
        }

        $endingAt = $this->ending_at;

        if($this->starting_at->isSameMonth($endingAt)) {
            return $this->starting_at->format('d') . '-' . $endingAt->format('d.m.Y');
        } else if($this->starting_at->isSameYear($endingAt)) {
            return $this->starting_at->format('d.m') . '-' . $endingAt->format('d.m.Y');
        } else {
            return $this->starting_at->format('d.m.Y') . '-' . $endingAt->format('d.m.Y');
        }
    }

    public function getStartingAtForHumansAttribute() : string {
        return Date::parse($this->starting_at)->format('j F Y');
    }

    public function getEndingAtForHumansAttribute() : string {
        return Date::parse($this->starting_at)
            ->addDays($this->unified_days)
            ->format('j F Y');
    }

    public function getDaysToAttribute() : int {
        return Carbon::parse($this->starting_at)->diffInDays(today());
    }

    public function getSignUrlAttribute() : string {
        if(! $this->has_free_reservations) {
            return "#";
        }

        return route('front.order.sign', ['courseTermId' => $this->id]);
    }

    public function hasActiveCourse() : bool {
        return $this->course instanceof Course && $this->course->is_active;
    }

    public function canSignByDate(): bool {
        return $this->starting_at > today()->format('Y-m-d');
    }
}

我的这部分代码有问题：

public function getCourserFromDates(string $dateFrom, string $dateTo) { return $this ->with('courseTerms') ->where('is_active', true) ->whereHas('courseTerms', function ($query) use ($dateFrom, $dateTo){ $query->whereBetween('starting_at', [$dateFrom, $dateTo]); $query->orderBy('starting_at'); }); }

这是我需要的：

日期在给定范围内的课程 按 startet_at ASC 对结果（course_terms）进行排序 目前，我的数据库中有 2020-12-22 - 2020-12-27 的记录。当我的 data_from 是 2020-12-25 时，我仍然可以看到 2020-12-22 的 course_terms。

如何解决这个问题？

标签： phplaravel

解决方案

您渴望courseTerms通过调用来加载关系with('courseTerms')。这不做任何约束/过滤。这将始终根据需要加载完整的关系。如果您想限制急切加载的内容，您需要通过使用匿名函数将其过滤掉来“限制急切加载”。在这种情况下，您可能会使用与whereHas调用相同的过滤：

$dates = [$dataFrom, $dateTo];

$filter = function ($query) use ($dates) {
    $query->whereBetween('starting_at', $dates)
        ->orderBy('starting_at');
};

return $this->where('is_active', true)
    ->with(['courseTerms' => $filter])
    ->whereHas('courseTerms', $filter);

推荐阅读