Sort pages by multiple date fields

[a-ok]

I have quite a lot of pages, all with a published date field and an event date field. The published date field 'article_published' is a required field but the event date field 'article_event_start_date' isn't so sometimes it won't be used.

What I am trying to do is sort the order of pages by published date first, then by the event date, which should mean that if a page has a published date of 08/09/16 and another page has an event date field of 07/09/16 then it would show before the page without an event date.

I thought the below would work but it seems to be ignoring the second sort selector. I even tried it without the group and same result. Is this possible?

<?php $articles = $pages->find("parent=/articles/, article_tags=$page, sort=-article_published, (sort=article_event_start_date)"); ?>

Also, just curious. I also have the date field 'article_event_end_date' but that is also optional. Is there a way to return all pages but exclude those which event end date has passed? I thought 'article_event_end_date>=today' but that isn't returning those pages that don't have the field filled out (whereas it should).

[LostKobrakai]

Not filled date fields are sorted as 0 therefore you cannot "fallback" to another field's date. Secondary sorting is only used to determine sorting for pages, which have an equal sorting value by the first field. It's like sorting items which are already put inside a folder from a previous sort.

The same goes for the >=today. NULL is not greater than the current timestamp, so the selector shouldn't return pages without end date.

Use "end=(end_date=''), end=(end_date>=today)" instead.

[a-ok]

Thanks, LostKobrakai.

I understand. I just thought it would sort it all by published date first, then the ones that have an event date, sort them into the first sort. You know?

Can you elaborate on "end=(end_date=''), end=(end_date>=today)" what is 'end'?

[LostKobrakai]

() are OR-groups and end= is just so that those two groups do not mix with other OR-groups in your selector. If they're the only ones you can skip this label.

Mixing fields for sorting simply isn't supported by processwire's selector engine. But it's possible using raw sql queries.

[Robin S]

10 hours ago, oma said:

I just thought it would sort it all by published date first, then the ones that have an event date, sort them into the first sort. You know?

I'm not 100% clear on what you mean, but you could create a datetime field "sort_date" and use a save hook to copy the event date to it if one exists, otherwise copy the published date. Then sort by sort_date in your selector.

[a-ok]

Thanks for your help. I really thought I could sort by multiple fields (if that field is blank then it simply skips over it and sorts by the first field).

Just to continue with this in a different light...

Do you think it's possible to remove pages from the find selector then prepend them to the bottom of the output? For example, most events use the start_event_date field, but ones that don't, instead of showing the date, show either 'Regular' or 'TBC'. Regular is shown if the user selects the checkbox 'article_event_regular' is ticked. TBC is shown if no start or end date is entered.

I'm wondering how I could remove these from the selector, but then append to bottom of the result.

$articles = $pages->find("parent=/articles/, article_tags=$page, sort=-article_published, end=(article_event_start_date=''), end=(article_event_start_date>=today)");

 

[a-ok]

On 09/09/2016 at 2:28 AM, Robin S said:

I'm not 100% clear on what you mean, but you could create a datetime field "sort_date" and use a save hook to copy the event date to it if one exists, otherwise copy the published date. Then sort by sort_date in your selector.

I also think this idea of if the event start date exists, then make this the published date...

$pages->addHookAfter('Pages::saveReady', function($event) {
	$page = $event->arguments('page');
	if ($page->article_tags->has("id=1339") && $page->article_event_start_date) { // Event and has blank start date
		$page->article_published = $page->article_event_start_date;
	}
});

 

[LostKobrakai]

You can just use two different find calls and just join them:

$top = $pages->find(…);
$bottom = $pages->find(…);

$all = $top->import($bottom);

And if you need pagination use this: https://github.com/LostKobrakai/Paginator

[a-ok]

30 minutes ago, LostKobrakai said:

You can just use two different find calls and just join them:

$top = $pages->find(…);
$bottom = $pages->find(…);

$all = $top->import($bottom);

And if you need pagination use this: https://github.com/LostKobrakai/Paginator

Thanks! How does it know to remove the $bottom results from the $top results? Is that part of import? It's done it for me... so just checking.

[LostKobrakai]

You'd should differentiate that in your selectors. There's nothing happening automatically.

[a-ok]

25 minutes ago, LostKobrakai said:

You'd need to differentiate that in your selectors. There's nothing happening automatically.

Weird... it seems to be doing just that. Anyway, will make sure there's a differentiation just in case.

[LostKobrakai]

A PageArray can only hold one instance of each page, so yeah it does, but it's not really explicit that way (and won't work as expected with my paginator)

[elabx]

Hi everyone! Reviving this. I have a PageArray with pages with two different templates, both with different date properties/fields which need to be used for sorting. 

Anyone got a suggestion for this?

EDIT:

Found this solution from @LostKobrakai which might work. I'd wonder if I could simply do the sorting right in the db like @LostKobrakai suggests.