Laravel基于评级的碳趋势

Question

I want to find out if a product is trending on my website (trending up or down) based on the period that is passed in. I.e. If week is passed in, it compares the current week up to the day to the previous week up to the same day. e.g. If today is Wednesday, and the mode is week, it will compare this week up to today, with last week up to last Wednesday.

I have so far:

public function getTrendAttribute($period)
{
    $last_period = $this->ratings()->where(DB::raw(), )->avg('rating');
    $this_period = $this->ratings()->where(DB::raw(), )->avg('rating');
}

Which I don't think is the correct start :(.

How can I achieve this (I have the Carbon library in my app)?

---

What about this? I am not sure if it is very efficient but it seems to be giving me some numbers that make sense:

public function getTrendAttribute($period)
{
    $tz = 'Europe/London';
    $this_week_start = Carbon::now($tz)->startOfWeek();
    $now = Carbon::now($tz);

    $last_week_start = Carbon::now($tz)->subWeek(1)->startOfWeek();
    $now_last_week = Carbon::now($tz)->subWeek(1);

    $last_period = $this->ratings()->where('created_at', '>=', $last_week_start)->where('created_at', '<=', $now_last_week)->avg('rating');
    $this_period = $this->ratings()->where('created_at', '>=', $this_week_start)->where('created_at', '<=', $now)->avg('rating');

    return $last_period > $this_period ? -1 : $last_period < $this_period ? 1 : 0;
}

Answer 1

Try this. Note that it is specific to the week grouping, and you would have to modify that query for grouping by month, or day, or anything else like that. But you should be able to use MySQL date functions to accomplish this.

$last_period = $this->ratings()->where(DB::raw('week(created_at)'), DB::raw('week(now()) - 1'))->where(DB::raw('dayofweek(created_at)'), '<=', DB::raw('dayofweek(now())'))->avg('rating');
$this_period = $this->ratings()->where(DB::raw('week(created_at)'), DB::raw('week(now())')->avg('rating');