雄辩的 WHERE 不返回预期的结果 - Eloquent WHERE does not return expected results

我希望下面的代码能够在每个$ratings->where旁边的注释中工作:

        $ratings = VideoRating::where('video_id', '=', $video->id)
    ->where('created_at','>=', 'Carbon'Carbon::createFromFormat('Y-m-d',$start_date))->where('created_at', '<=',  'Carbon'Carbon::createFromFormat('Y-m-d',$end_date));
    $data = [
    "likes"     => $ratings->where('rating','=',1)->get()->count(), //0
    "dislikes"  => $ratings->where('rating','=',-1)->get()->count(), //1
    "not_sure"  => $ratings->where('rating','=',0)->get()->count(), // 0
    ];

数据库中只有一个评级为 -1，其他值 sould 产生 0 作为计数。但是，在所有情况下我都得到 0。

让我思考的是，如果我用 -1 而不是 1 更改第一个"喜欢"，我会得到喜欢和"不喜欢"的结果为 1，但"not_sure"的结果仍然是 0。

我不明白为什么会这样。

编辑

数据库仅包含一条记录

INSERT INTO "public"."video_ratings(id,user_id,video_id,rating,created_at, updated_at)" VALUES ('1', '2', '1', '-1', '2016-02-24 14:57:41', '2016-02-24 14:57:41');

而且我使用的日期01/02/2016到29/02/2016，所以它在created_at的时间段内

编辑 2

这肯定与改变$ratings变量有关。虽然我不太精通 Eloquent 确切地知道如何，但下面的代码按预期工作

        $ratings = VideoRating::where('video_id', '=', $video->id)
        ->where('created_at', '>=', 'Carbon'Carbon::createFromFormat('Y-m-d', $start_date)->toDateTimeString())->where('created_at', '<=', 'Carbon'Carbon::createFromFormat('Y-m-d', $end_date)->toDateTimeString());

    $ratings1 = VideoRating::where('video_id', '=', $video->id)
        ->where('created_at', '>=', 'Carbon'Carbon::createFromFormat('Y-m-d', $start_date)->toDateTimeString())->where('created_at', '<=', 'Carbon'Carbon::createFromFormat('Y-m-d', $end_date)->toDateTimeString());
    $ratings2 = VideoRating::where('video_id', '=', $video->id)
        ->where('created_at', '>=', 'Carbon'Carbon::createFromFormat('Y-m-d', $start_date)->toDateTimeString())->where('created_at', '<=', 'Carbon'Carbon::createFromFormat('Y-m-d', $end_date)->toDateTimeString());
    $data = [
        "likes" => $ratings->where('rating', '=', 1)->get()->count(),
        "dislikes" => $ratings1->where('rating', '=', -1)->get()->count(),
        "not_sure" => $ratings2->where('rating', '=', 0)->get()->count(),
    ];

我不喜欢它，我更喜欢一种正确的方法来重用初始对象$ratings

如果您希望从同一基本查询中获得不同的结果，则需要在执行 get/count 方法之前克隆它：

$likes = clone $ratings;
$dislikes = clone $ratings;
$not_sure = clone $ratings;
$data = [
    "likes"     => $likes->where('rating', 1)->count(),
    "dislikes"  => $dislikes->where('rating', -1)->count(),
    "not_sure"  => $not_sure->where('rating', 0)->count(),
];

由于在 PHP 中实现对象的方式，可能会出现您的问题

尝试使用 Eloquent 中的查询范围：

编辑VideoRating类并添加以下方法

public function scopeFetchRatingsBetweenDatesForVideo($query, $videoID, $startDate, $endDate, $rating = null){
    $query->whereVideoId(videoID)
        ->whereRaw('DATE_FORMAT(`created_at`, "%Y-%m-%d") >= ?', [$startDate])
        ->whereRaw('DATE_FORMAT(`created_at`, "%Y-%m-%d") <= ?', [$endDate])
    if($rating != null){
        $query->whereRating($rating)
    }
    return $query
}

使用您的范围

如果输入的开始日期和结束日期的格式为 Y-md-d，则不应将它们转换为 Y-m-d H：i：s。一种选择是将它们用作 Y-m-d 并将created_at字段转换为 Y-m-d。如果您的输入日期为 Y-m-d H：i：s，请随时将输入日期与created_at字段进行比较。

 $startDate = 'Carbon'Carbon::createFromFormat('Y-m-d',$start_date)->toDateString();
 $endDate = 'Carbon'Carbon::createFromFormat('Y-m-d', $end_date)->toDateString();
 $data = [
    'likes' => VideoRating::fetchRatingsBetweenDatesForVideo($video->id, $startDate, $endDate, 1)->count(),
    'dislikes' => VideoRating::fetchRatingsBetweenDatesForVideo($video->id, $startDate, $endDate, -1)->count(),
    'not_sure' => VideoRating::fetchRatingsBetweenDatesForVideo($video->id, $startDate, $endDate, 0)->count(),
];
// or
$ratings = VideoRating::fetchRatingsBetweenDatesForVideo($video->id, $startDate, $endDate);
$data = [
    'likes' => (clone $ratings)->whereRating(1)->count(),
    'dislikes' => (clone $ratings)->whereRating(-1)->count(),
    'not_sure' => (clone $ratings)->whereRating(0)->count(),
];