CODEWITHSUNDEEP
phplaraveleloquent
idipous
idipous

Reputation: 2910

Eloquent WHERE does not return expected results

I would expect the code below to work as it looks in the comment next to each $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
    ];

There is only one rating with -1 in the database and the other values sould yield 0 as count. However I get 0 in all cases.

What got me thinking though is the fact that if I change the first "likes" where with -1 instead of 1 I get a result of 1 for both likes and "dislikes" but it remains 0 for "not_sure".

I cannot understand why this happens.

Edit

The database contains only one record

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

And the dates I use are 01/02/2016 to 29/02/2016 so it is within the created_at period

EDIT 2

Definitely it has to do with mutating the $ratings variable. Although I am not well versed in Eloquent to know exactly how but the code below works as expected

        $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(),
    ];

I don't like it though and I would prefer a proper way to reuse the initial object $ratings

Upvotes: 1

Views: 81

Answers (2)

Moinescu Mihai
Moinescu Mihai

Reputation: 21

Your problem could appear because of the way objects are implemented in PHP

Try using the query scopes in Eloquent:

  1. Edit your VideoRating class and add the following method
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
}
  1. Use your scope

If your input start and end dates have the format Y-md-d, you shouldn't be translating them to Y-m-d H:i:s. An option would be using them as Y-m-d and translating the created_at field to Y-m-d. If your input dates are Y-m-d H:i:s feel free to compare the input dates with the created_at field.

 $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(),
];

Upvotes: 1

Tomas Buteler
Tomas Buteler

Reputation: 4117

If you want different results from the same base query, you need to clone it before executing the get / count methods:

$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(),
];

Upvotes: 1

Related Questions