Sorting events by date with multiple event dates stored in repeaters

[lpa]

I have a list of events that may have 1 to multiple event dates. The dates and places of the events are stored in repeaters. 

 

I would like to sort my eventlist by date:

 

Event 1

eventdate 1: 1.11.2013 15.00

eventdate 2: 15.11.2013 18.00

 

Event 2

eventdate 1: 5.11.2013 15.00

 

Event 3

eventdate 1: 14.11.2013 15.00

eventdate 2: 15.11.2013 18.00

eventdate 3: 16.11.2013 19.00

 

Event 4

eventdate 1: 17.11.2013 18.00

 

The repeater for the eventdetails has fields eventdetails.date and eventdetails.place. The event itself is stored with the template  "event".

 

I have tried the following:

$events = $pages->find("template=event, sort=eventdetails.date");

This gives me an error: Column not found: 1054 Unknown column '_sort_eventdetails_date.date' in 'order clause'.

 

And this:

$events = $pages->find("template=event")->sort("eventdetails.date"); 

This gives me the correct order, but also an empty repeater item with empty eventdetails.date while looping the events: 

foreach ($events as $e) {
    foreach ($e->eventdetails as $d) {
        $o .= strftime("%e.%m.%G %k:%M",strtotime($d->date)).", {$d->place}";
    }
    $o .= $e->title;
}

Output for event 1

 

1.11.2013 15:00, place1

15.11.2013 18:00, place2

1.01.1970 2:00,

Event 1

 

What is the correct way to sort by the date field in the repeater? Why do I get the empty repeater line (1.01.1970 2:00) in the second example?

[lpa]

Can anyone help me with this? Or should I try to explain this more clearly?

[Mats]

Hey Ipa!

Could it be an empty ready item? If so try to skip the empty item(s) by doing something like:

foreach ($e->eventdetails->filter("date!=") as $d) {...

[lpa]

That filtering works! Thanks, but still...

There shouldn't be any empty items. So I don't understand where that empty item  comes from.

When trying count($e->eventdetails) I get two instead of one. Why is there two repeaters items even though I have defined only one in backend. And why do I get those two when using find()->sort() and only when when trying to use find(sort=eventdetails.date)?

[nik]

Those empty items you're seeing are indeed the repeater's "ready items" like Mats pointed out. But while that filter works for you now, I wouldn't rely on the date being empty: when building a test case I changed some formatting options for the date field after already having created some events and ended up in a situation where the dates weren't empty for the pre-existing events. So I'd recommend filtering by the status to exclude unpublished items (filter("status=1")).

But this is only a work-around as we're dealing with a bug here. A bug that raises its head only when certain conditions are met. Like you're saying, the extra items appear only after sorting by "eventdetails.date". To be more specific, the items appear whenever the PageArray is being filtered or sorted with a selector using a field inside the repeater. What's even more interesting is that this only happens for "untouched" repeaters. If the repeater contents is referenced in any way before filtering this actually works!

OK, this is getting weird enough. I'm filing a bug with some instructions for Ryan to reproduce this odd behavior. I'll edit this post and add a link to the issue a bit later.

Edit:

See the issue here for some more details: https://github.com/ryancramerdesign/ProcessWire/issues/275

[ryan]

I fixed the issue referred to in Nik's GitHub issue report (thanks Nik). 

As for sorting the items in the repeater, I think the situation you are trying to accommodate here is a little unusual. Wouldn't the date field be associated with the 'event' rather than for some repeatable field within the repeater? It can't sort on eventdetails.date because it's not referring to any single date, but rather any number of potential dates. What I'd suggest is adding an 'date' field to your 'event' template that represents the event starting date. But if you want it to work with what you've already got, then I suppose you could query the repeaters rather than the events (though I've not yet tried this):

$events = new PageArray(); 
$items = $pages->find("template=repeater_eventdetails, date>0, sort=date, include=all"); 
foreach($items as $item) {
  $event = $item->getForPage();
  if($item->isPublic() && !$events->has($event)) $events->add($event); 
}

[lpa]

Thanks for the fix! I'll take the latest version. 

Well, the events are concerts that are held many times in different dates with the same program. I am trying to find out how to show a list of upcoming concerts in correct order even though some of the actual concert dates in the repeater have passed the day I create the list. I would like to search all the concerts that have a date in the repeater in the future and sort by that date:

$events = $pages->find("template=event,eventdetails.date>today,sort=eventdetails.date");

But I suppose your example will do the trick. I'll try that, thanks!

[lpa]

The selection now is working nice.

But how could I then enable the pagination for these events that are now in the $events array?

If I try to use limit=10 in the find() above, it doesn't work, because I shouldn't limit the repeaters. 

[lpa]

I tried to use the following to get the pagination

$events = new PageArray(); 
$items = $pages->find("template=repeater_eventdetails, date>0, sort=date, include=all"); 
foreach($items as $item) {
  $event = $item->getForPage();
  if($item->isPublic() && !$events->has($event)) $events->add($event); 
}

$events = $events->find("limit=10"); 

foreach ($events as $event) {
   echo $event-title;
}

echo renderNavigation($events);
 

This gives me the event-titles and pagination, but when I click the next page on the pagination, the page won't change. What should I do???

Another question on this is that the rendering of the event-list on my pages is a bit slow. How should I debug the page rendering time? I would like to know if there is some problems with my pages or with the code. 

[lpa]

I would really like to know if there is an easy way to enable the pagination in my code above? Or what would you suggest?

[Soma]

There's various threads about what you ask already. Some ways to do it you'll find in my gist examples https://gist.github.com/somatonic/5420536#file-paginator_manual-php

As for debugging rendertime you can use Debug class:

$rendertime = Debug::timer();

// do your stuff

$rendertime = Debug::timer($rendertime);
echo "rendertime: $rendertime";

[lpa]

Thanks Soma! I will use your example for pagination. I am not very good finding things on this forum...

A new question regarding repeaters and selectors:

Trying to use the find above with some fields from the actual page containing the repeater or even the parent of the page containing the repeater. 

$items = $pages->find("template=repeater_eventdetails, date>0, sort=date, include=all, parent=somepagename"); 

Is this possible or what would be the correct syntax to find the pages containing these repeaters, that have parent "somepagename"?

 

Or is there selector like: has_parent=somepagename or getForPage=somepagename?

[ryan]

By parent, do you mean the page that the repeater lives on, or the parent of the page that the repeater lives on? If parent is the page that the repeater lives on, then there'd be no reason to query it since you could just access $page->repeaterField->find(). So I'm assuming you mean parent of the page that the repeater lives on. That's a tougher question, and not sure there is a good answer since repeaters are disconnected from the site's structure (they have their own structure off in the admin). The only physical connection between the repeater items and the page they represent is the repeater page name, which uses the syntax "for-page-[id]". Currently I can't think of a straightforward way to perform that query, at least in a manner that would be scalable to tens of thousands of items. A non-scalable way to do it would be:

$selector = "template=repeater_eventdetails, date>0, sort=date, include=all, name=";
$parent = $pages->get('/some/parent/'); 
foreach($parent->children as $p) $selector .= "for-page-{$p->id}|";
$items = $pages->find(rtrim($selector, '|')); 

[lpa]

Thanks, I'll try this.

I think it would be nice to have some selectors for searching the pages and parents the repeaters are on. Some way to treat the repeater fields in the find() as they were normal fields of a page that have a parent.

[Soma]

I don't think that would be easy possible as Ryan explained. Repeaters are limited to what you can do. I would think you're better off using child pages under event to do what you want. It would be more correct from a data structure point and open up more selector friendly handling.

[lpa]

I don't want to use pages for the concert date and place, because the user interface is more friendly with repeaters. 

I am still struggling with my selectors. With Ryans example in message #6 I get a PageArray with all the future events. Then I try to filter the events based on the parent with this: 

$events = $events->filter("parent=/path/to/parent/");

This works if I use filter("parent=4352"), where 4352 is the id of the parent in /path/to/parent/, but not with the path to the parent. How can this be possible? 

[ryan]

This works if I use filter("parent=4352"), where 4352 is the id of the parent in /path/to/parent/, but not with the path to the parent. How can this be possible? 

Specifying /path/to/parent/ is only supported by database-driven selectors at present. That would be a good one for us to add to the in-memory selectors. You could do this though:

$parent = $pages->get('/path/to/parent/');
$events = $events->filter("parent=$parent"); 

Which is essentially the same as what you did:

$events->filter("parent=4352"); 

[lpa]

Thanks Ryan for this help. I think it would help if the API documentation would describe when I can use filter('/path/to/parent/') and when not. :-D

I feel sometimes that the documentation should be more detailed and contain more examples. But the new cheatsheet seems very promising.