Limit returns one less record than it should

I’m using the following to get the last 4 events that are after the current date ($filterBy = date(‘Y-m-d’)), but only 3 records are returned. If I increase the limit to 5, then 4 records are returned. Any ideas?

$events = $page->children()->listed()->when($filterBy, function($filterBy) {
      return $this->filterBy('from', 'date >=', $filterBy);
  })->sortBy('from', $direction = 'asc',)->limit(4);

Hey Tina, this is weird, no idea, why this could be happening. Which Kirby version are you using? Any plugins?

Just on a side note, there shouldn’t be a trailing comma and the variable name is superfluous, so sortBy('from', 'asc'), but that’s not the reason why it’s failing.

1 Like

Thanks Sonja, ahh yes - you’re right of course - fixed that but as you say didn’t make a difference.

BTW paginate(4) instead of limit(4) also has this problem.

You didn’t answer my questions, though…?

Ups sorry, I’m using the latest version → 3.9.0 – Brookesia.
I only have one plugin installed, but I’m not using it with a template related to this query → kirby-plugin-custom-add-fields

I’ll try removing the plugin and will look if it makes a difference (I doubt it though).
Update: Removing the plugin made no difference.

Could you please post the complete controller and template/snippets used in the template (and model, if you use one).

Sure (there is no controller or model):

template/snippet:

<?php
  $eventsPage = page('events');
  $current_date = date('Ymd');
  $filterBy = date('Y-m-d 00:00:00');
  
  $events = $eventsPage->children()->listed()->when($filterBy, function($filterBy) {
      return $this->filterBy('from', 'date >=', $filterBy);
  })->sortBy('from', 'asc')->limit(4);
  
?>
<div class="gallery-bg">
<div class="text">
<span class="float-right"><a href="events" class="link-hover">View all</a></span>
<h2>Upcoming Events</h2>
</div>
<table class="events-table">
<?php foreach ($events->listed() as $event): ?>
<?php if ($current_date < $event->to()->toDate('Ymd') == true): ?>
<tr>
<td><a class="link-hover" href="<?= $event->url() ?>"><?= $event->title()->esc() ?></a></td>
<td><?= $event->from()->toDate('D, d M Y') ?> - <?= $event->to()->toDate('D, d M Y') ?></td>
<td><?= $event->from()->toDate('g.i a') ?> - <?= $event->to()->toDate('g.i a') ?></td>
</tr>
<?php endif ?>
<?php endforeach ?>
</table>
</div>

PS: for test purposes I created a new template with nothing else in it but the above snippet. I also removed the limit() and sortBy() but it still it renders 1 less record than it should.

I guess it’s this condition that removes the entry.

Two more notes, though (not related to the issue):

listed() is superfluous, you have already filtered by listed.

This is not a correct date format, either use the proper format values (H:i:s), or remove the zeros.

1 Like

Also, instead of using the if statement, add it to the filter, so that limit works as expected

1 Like

Thanks so much Sonja! :grinning: :pray:
Yes, that was the problem, just had to replace “<” with “<=”.

I can’t replace the 00:00:00 for H:i:s though in the filterBy variable, because that would give me the current time, but I want to have all events starting from today.

Anyway, thanks again and sorry for being blind!

The just use date('Y-m-d') without the zeros.

1 Like

Hmm, not sure how I’d go about that … :thinking:

Yeah, I had that initially but then it was missing an event that was today, but had already past (e.g. the event was at. 3pm and it was now 4pm). So to make sure I get that one, I filter by datetime that starts today at 0 hours.

  $eventsPage = page('events');
  $current_date = date('Y-m-d');

  
  $events = $eventsPage->children()->listed()
     ->when($filterBy, function($filterBy) {
      return $this->filterBy('from', 'date >=', $filterBy);
  })
     ->when($current_date, function($current_date) {
      return $this->filterBy('to', 'date <=', $current_date);
  })
     ->sortBy('from', 'asc')->limit(4);

although, I don’t see much sense in using when() here and would shorten this to

$events = $eventsPage
  ->children()
  ->listed()
  ->filter(fn ($event) => $event->from() >= $current_date && $event->to() <= $current_date)
 ->sortBy('from', 'asc')->limit(4);
1 Like

perfect Sonja - thank you

that worked beautifully and is indeed so much simpler. Needed a slight modification (I don’t want to use the “to()” date as otherwise I’d only get today’s events, but I want those that are today and ones in the future).

$events = $eventsPage
  ->children()
  ->listed()
  ->filter(fn ($event) => $event->from() >= $current_date)
->sortBy('from', 'asc')->limit(4);

So now I was able to also change $current_date = date('Y-m-d H:i:s'); to the proper date format :slight_smile: