Last active
August 5, 2016 16:30
-
-
Save ctorgalson/6ffa553810052424253a3ca46f0a8b75 to your computer and use it in GitHub Desktop.
Simplified date sort query
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
<?php | |
/** | |
* Called to add the sort to a query. | |
*/ | |
public function query() { | |
$this->ensureMyTable(); | |
$configuration = [ | |
'table' => 'node__field_event_date', | |
'field' => 'entity_id', | |
'left_table' => $this->tableAlias, | |
'left_field' => $this->realField, | |
'operator' => '=' | |
]; | |
$join = Views::pluginManager('join')->createInstance('standard', $configuration); | |
$this->query->addRelationship('node__field_event_date', $join, $this->tableAlias); | |
// Create a DATETIME value here to use instead of UNIX_TIMESTAMP(). | |
$dt = new DateTime("now", new DateTimeZone("America/Winnipeg")); | |
$current_datetime = $dt->format('Y-m-d H:i:s'); | |
// Eliminate UNIX_TIMESTAMP() call (no transformation needed). | |
$date_alias = "node__field_event_date.field_event_date_value"; | |
// Is this event in the past? | |
$this->query->addOrderBy(NULL, | |
// Eliminate UNIX_TIMESTAMP() call (now comparing DATETIME values). | |
"$current_datetime > $date_alias", | |
$this->options['order'], | |
"in_past" | |
); | |
// How far in the past/future is this event? | |
$this->query->addOrderBy(NULL, | |
// Eliminate UNIX_TIMESTAMP() call (now comparing DATETIME values). | |
"ABS($date_alias - $current_datetime)", | |
$this->options['order'], | |
"distance_from_now" | |
); | |
} | |
} |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment