Laravel simplePaginate repeating many data on different page

Here is my code:

$upcoming_collections = Collection::upcoming()->with( 'watchlists','blockchain')->where('status', 'published');
if($blockChain){
    $upcoming_collections =   $upcoming_collections->whereRelation('blockchain', 'slug',$blockChain);
}
$upcoming_collections  = $upcoming_collections->orderBy('release_date','asc')->orderBy('last_day_vote_count', 'desc')->simplePaginate($count);

This is showing many duplicate records on some other pages. And upcoming is a local scope defined as:

public function scopeUpcoming($query): \Illuminate\Database\Eloquent\Builder
{
    return $query->where('release_date', '>=', now())->orWhere('release_date', null)->orWhere('to_be_announced', 1);
}

Here, the release date is a nullable DateTime field, and last_day_vote_count is the integer column. This is repeating items on clicking next page, on multiple pages.

But, if I try without ordering, something is as follows. It works fine.

$upcoming_collections  = $upcoming_collections->simplePaginate($count);

Snapey

4 years ago

Level 122

your scope messes with other where statements by virtue of having orWhere. you need to wrap these in a single where

public function scopeUpcoming($query): \Illuminate\Database\Eloquent\Builder
{
    $query->where(function ($query) {
        $query->where('release_date', '>=', now())
                ->orWhereNull('release_date')
                ->orWhere('to_be_announced', 1);
        });
}

Inquisitive

4 years ago

Level 9

@Snapey Thank you snapey, this is definitely an issue that I forgot to wrap. But still, it is showing the same results of duplication.

Inquisitive

4 years ago

Level 9

@Snapey This seems to be the query running on clicking view more on first page

select * from `collections` where `status` = ? and (`release_date` >= ? or `release_date` is null or `to_be_announced` = ?) order by `release_date` desc, `last_day_vote_count` desc limit 13 offset 12

and bindings:

"bindings" => array:3 [
      0 => "published"
      1 => Illuminate\Support\Carbon @1652663986 {#1599
        #endOfTime: false
        #startOfTime: false
        #constructedObjectId: "0000000059f3b7ac00000000500ec9f4"
        #localMonthsOverflow: null
        #localYearsOverflow: null
        #localStrictModeEnabled: null
        #localHumanDiffOptions: null
        #localToStringFormat: null
        #localSerializer: null
        #localMacros: null
        #localGenericMacros: null
        #localFormatFunction: null
        #localTranslator: null
        #dumpProperties: array:3 [
          0 => "date"
          1 => "timezone_type"
          2 => "timezone"
        ]
        #dumpLocale: null
        #dumpDateProperties: null
        date: 2022-05-16 01:19:46.331307 UTC (+00:00)
      }
      2 => 1

Snapey

4 years ago

Level 122

So either you actually have duplicate records or release_date or last_day_vote_count is actually changing

Please or to participate in this conversation.