Ordering by date reversed, then time normal order

[adrian]

OK, here's a tricky one.

I want to group event items by day in reverse order so that most recent is on top, but then with multiple events on the same day, I want them ordered by time of day in normal order.

With SQL I could easily do: ORDER BY DATE(date_time) DESC, TIME(date_time) ASC

Anyone have any ideas how best to do this in PW with a selector?

I am thinking I might just end up sorting the page array afterwards, but thought someone might have a cooler idea. 

Ryan - any chance you could implement mysql's date and time functions so something along these lines would be possible:

"sort=-date_time->date, sort=date_time->time" 

[adrian]

Ok, here is what I have ended up using for the moment:

$results = new PageArray();
foreach($page->children("limit=10, sort=-date_time") as $p){
    $date_time = $p->date_time;
    $p->date = date("Y-m-d",$date_time);
    $p->time = date("Gis",$date_time);
    $results->add($p);
}
$results->sort("time")->sort("-date");

Definitely not thoroughly tested yet.

These results are being paginated, hence the limit=10. I guess it might be possible if the 10th and 11th items are on the same day for this to put these out of time order. I guess I could load more results and then truncate to 10 after sorting, although that will mess with the pager module. Will think about it some more - might just go back to an SQL based option to populate the $results array in the first place.

Any thoughts?

[kongondo]

Would the topics below help? Maybe am not getting you

http://processwire.com/talk/topic/3290-sort-by-date-range/

http://processwire.com/talk/topic/4124-where-to-find-description-of-eg-1-month-selectors/

[adrian]

Hey kongondo,

Thanks, but what I am trying to achieve is ordering like this:

July 25
- 9am
- 12pm
- 2pm

July 23
- 8am
- 10am

My page array sorting works, and there are lots of other ways to achieve the same results (eg. population of the page array using sql directly, restructuring the page tree to have day based children and then time based grandchildren), but I just feel like there should be a way to do this with selectors to manipulate the sql query.

I am actually wondering how difficult it would be to facilitate the use of all mysql functions and operators in selectors, not just DATE and TIME like I needed for above.

I am on vacation this week, but if Ryan hasn't chimed in on this thread by the time I am back, I might post the idea as a feature suggestion and see what he thinks.

[horst]

Adrian, you also may save the timestamp for events into 2 different fields on each page, (using a module or something that splits the timestamps and populate both fields).

This way you can use existing selectors.

[adrian]

Hey Horst,

I actually tried separating the date and time fields, but there was no way to make a user friendly time-only field - they would have to manually type the time.

However, I didn't think of automatically splitting them with a module - nice idea!

[horst]

Yes, you can set them to hidden / invisible fields and hook into page-save or better save-ready. Than you populate the fields and your customers only see and use the datepicker.

[ryan]

Would something like this work? (written in browser, so might need tweaking). This should print all events sorted by date descending, but grouped by day and sorted by time ascending within the day. 

$allEvents = $pages->find("parent=/events/, sort=-date"); 
while($allEvents->count()) {
  $event = $allEvents->first();
  $from = strtotime(date('Y-m-d 00:00', $event->getUnformatted('date')));
  $to = strtotime("+1 day", $from); 
  $events = $events->find("date>=$from, date<$to, sort=date"); 
  echo "<h3>Events on " . date('F j, Y', $from) . "</h3>";
  foreach($events as $event) {
    echo "<p><a href='$event->url'>$event->title</a> $event->date</p>";
    $allEvents->remove($event); 
  }
} 

[adrian]

Hey Ryan, Thanks for another option - there are lots of ways to do this for sure. Any thoughts on the ability for PW to support all mysql functions and operators within selectors? Maybe haven't thought it through fully, but shouldn't it be easy to support? eg: sort=date_time->time() would be converted into ORDER BY TIME(date_time)

[ryan]

Any thoughts on the ability for PW to support all mysql functions and operators within selectors? 

This has never been the goal with selectors. I think we'd have an unusually complex selector system if it could entirely replace SQL. SQL is very powerful and–relative to it's power–probably as simple as it can be. So when needs come up that are more easily accomplished by SQL, that's the way to go. The goal with selectors is to remain simpler than SQL for the majority of cases, but not to replace everything you can do with SQL. No doubt we'll continue adding new options and capabilities to selectors in the future, but it's always got to be a balance in focusing on the things that will get the kind of usage and mileage that warrant their inclusion. If the sorting by date then reverse by time function that you mention comes up regular need for many people, then it may be worth adding at some point. 

[adrian]

Hey Ryan,

I think maybe I wasn't clear I am not suggesting that you need to replicate sql functionality, just allow sql functions to be included in the selectors. I haven't looked over the core code yet to see how the selector string gets translated into an sql string, but would it be too difficult to allow anything like:

field_name->sqlfunction() to be translated into SQLFUNCTION(field_name) to be translated into SQLFUNCTION(field_name)

This would allow all these: http://dev.mysql.com/doc/refman/5.0/en/func-op-summary-ref.html to be used directly. Obviously the date and time functions would prove useful, but I am sure lots of others would be handy as well.

Maybe it is more complicated to implement than I realize, or maybe there are just lots of good reasons not too

[Soma]

As Ryan already said use direct sql then.

[adrian]

Hey Soma,

Of course direct SQL works, but it can become much more complicated if you are trying to limit page results to certain parents, templates, etc. Not difficult once you understand PW db table structure and if you know how to use SQL JOINS, but more complex nonetheless.

In this case it looks something like:

SELECT id FROM pages
INNER JOIN field_date_time ON field_date_time.pages_id = pages.id
WHERE parent_id = " . $page->id . "
ORDER BY DATE(data) DESC, TIME(data) ASC
LIMIT " . (($input->pageNum-1)*10) . ",10

The above works and /page2/ etc urls work, but MarkupPagerNav no longer automatically generates the page link buttons.

Do know the best way to trigger MarkupPagerNav to generate these buttons?

I actually just discovered that one of the limitations to my approach above (http://processwire.com/talk/topic/4257-ordering-by-date-reversed-then-time-normal-order/?p=41709) is that generation of the page button links doesn't work either, so I still don't have a fully working solution. I am thinking that I might need to go with Horst's suggestion of a module to split the field into separate hidden date and time fields - won't be difficult, but goes against the principles of database redundancy.

[Soma]

Yeah it's not actually simple.

An approach would be to create pager manually using the core PageNav class.

$limit = 8;
$sql = "SELECT id FROM pages
        INNER JOIN field_mydate ON field_mydate.pages_id = pages.id
        WHERE status < 2048 AND templates_id = " . $templates->get("basic-page")->id . "
        ORDER BY DATE(data) DESC, TIME(data) ASC LIMIT " . (($input->pageNum-1)*$limit) . ",$limit";

// run query
$res = $db->query($sql);

// output events
while($row = $res->fetch_array()) {
    $event = $pages->get($row['id']);
    echo "<p><a href='$event->url'>$event->mydate | $event->title</a></p>";
};

// generate pager navigation manually
include($config->paths->MarkupPagerNav . "PagerNav.php");
$total = $pages->count("template=basic-page, mydate>0");
$pager = new PagerNav($total, $limit, $input->pageNum);
$pagerMarkup = '';
foreach($pager as $link) {
    $class = $link->pageNum == $input->pageNum ? 'on' : ''; // is it the current page?
    if($link->type == 'separator') $item = '…'; // is it a separator
        else $item = "<a class='$class' href='{$page->url}page{$link->pageNum}/'>$link->label</a>"; // or a normal link
    $pagerMarkup .= "<li>$item</li>";
}

// output pagernav
echo "<ul class='pagernav'>$pagerMarkup</ul>";
 

Taken from one of my examples: https://gist.github.com/somatonic/5420536

[Soma]

....or simpler version using a "dummy" page array that searches the same pages but not actually sorting, just for the the pager.

$limit = 4;
$sql = "SELECT id FROM pages
        INNER JOIN field_mydate ON field_mydate.pages_id = pages.id
        WHERE status < 2048 AND templates_id = " . $templates->get("basic-page")->id . "
        ORDER BY DATE(data) DESC, TIME(data) ASC LIMIT " . (($input->pageNum-1)*$limit) . ",$limit";

// run query
$res = $db->query($sql);

// output events
while($row = $res->fetch_array()) {
    $event = $pages->get($row['id']);
    echo "<p><a href='$event->url'>$event->mydate | $event->title</a></p>";
};

// generate pager navigation using dummy array
$dummies = $pages->find("template=basic-page, mydate>0, limit=$limit");
echo $dummies->renderPager();

[adrian]

Hey Soma,

Thanks for both examples - I really appreciate you seeing me through on this one! 

I decided to go with the dummy array for generating the pager links - it works perfectly! and it easily allows me to use either the direct SQL approach, or the original idea I showed in post #2.

What I am now wondering is if it would be at all possible to have PW parse a selector that uses a combination of PW selectors and  SQL such that you could write something like:

$results = $pages->find("template=x, limit=10, sort={ORDER BY DATE(data) DESC, TIME(data) ASC}");

Not sure on the syntax, but the idea is that if the selector parser comes across something inside curly braces (or some other delimiter), it uses that directly in the final SQL query. Maybe in this example you could even leave out the "ORDER BY " as the sort would be inserting that anyway. Maybe this could get way too complex to deal with, so I won't bring it up again

[ryan]

I think maybe I wasn't clear I am not suggesting that you need to replicate sql functionality, just allow sql functions to be included in the selectors. I haven't looked over the core code yet to see how the selector string gets translated into an sql string, but would it be too difficult to allow anything like:

 

field_name->sqlfunction() to be translated into SQLFUNCTION(field_name) to be translated into SQLFUNCTION(field_name)

 

This would allow all these: http://dev.mysql.com...ummary-ref.html to be used directly. Obviously the date and time functions would prove useful, but I am sure lots of others would be handy as well.

I'm open to this sort of thing in selectors, especially if it can be added on as easily as you mentioned. But it probably belongs as a module rather than part of the core selectors, because selectors don't imply "database". That is just one of the contexts of selectors, and we've been working hard to make the system as consistent as possible across contexts. So I'm a little reluctant about introducing database-specific functions into selectors in the core. But would be enthusiastic about providing new ways for modules to hook into selectors to add their own functionality, if possible. 

[adrian]

Excellent - I am not sure the best way to achieve this yet, but if you can make it possible to hook into the selector parser, I would happy to have a go a writing a module that allows for using SQL directly in selectors, or at least using mysql function/operators. Not sure the best option yet. The former could be very powerful, but obviously more complex to integrate without problems. I'll have to start looking through the core and getting an idea how the parser works.

[ryan]

Probably what is needed is a hook into the PageFinder class, rather than into the selector parser. If a value is wrapped in {brackets} like you mentioned above, a custom hook could be called. PageFinder is admittedly one of the more complex parts of PW, so I don't necessarily see any solution as being particularly simple to implement. But here is the function that deals with sort values in PageFinder if you are interested. I could add a condition in that function that calls a hook like PageFinder::handleCustomSortValue when it finds the value wrapped in brackets.