Selector. Where now between start date and end date or now bigger than start date and end date is empty

[Elchin]

Hi.

I want select pages where now between date and end_date or now bigger than date and end_date is empty.

I have five tried variants:

$start = strtotime(date('Y-m-d') . " 00:00:00");
$results = $page->children("foo=(date<$start,date_end=''),bar=(date<$start,date_end>=$start),sort=-date,limit=12");

$start = strtotime(date('Y-m-d') . " 00:00:00");
$results = $page->children("date<$start,(date_end='',date_end>=$start),sort=-date,limit=12");

$start = strtotime(date('Y-m-d') . " 00:00:00");
$results = $page->children("date_end=''|date_end>=$start,date<$start,sort=-date,limit=12");

$start = strtotime(date('Y-m-d') . " 00:00:00");
$results = $page->children("!date_end|date_end>=$start,date<$start,sort=-date,limit=12");

$start = strtotime(date('Y-m-d') . " 00:00:00");
$results = $page->children("date_end>=$start|!date_end,date<$start,sort=-date,limit=12");

All this variants not worked for me and returned zero results.

[Autofahrn]

Guess you have to compare timestamps not the formatted values:

Edit: Sorry, beeing blind, you're already using timestamps...

[Autofahrn]

ok, tested a little and the selectors seem to work for me, primarily tested with this one:

$results = $page->children("!date_end|date_end>=$start,date<$start,sort=-date,limit=12");

Did you try outputting your date and date_end fields unformatted to see if they contain proper values?

[flydev]

Hi @Elchin

I got them in the past but the query was to slow so I finally used a custom query to achieve that, you might be interested as I don't know the number of pages your trying to fetch - I give you a small module which could be extended, shipped with a hook function which return all pages between two dates given a fieldname and a template id :

 

  Reveal hidden contents

<?php namespace ProcessWire;


class MyPageHooks extends WireData implements Module {

  public static function getModuleInfo() {

      return array(
          'title'    => 'MyPageHooks',
          'version'  => '1.0.0',
          'summary'  => 'Adds new methods to $page',
          'singular' => true,
          'autoload' => true,
          'author'   => 'flydev',
          'href'     => 'https://processwire.com/talk/topic/21021-selector-where-now-between-start-date-and-end-date-or-now-bigger-than-start-date-and-end-date-is-empty/'
      );
  }

  public function init() {
    $this->addHook('Page::getPagesFromDateRange', $this, 'getPagesFromDateRange');
  }

  /**
  * Hook getPagesFromDateRange(string fieldName, string $dateStart, string $dateEnd, int $templateId)
  * date format: YYYY/MM/DD
  * 
  * Usage:
  * 
  * 
  *  Get pages between `dateStart` and `dateEnd` with template id 53 from fieldname 'date1'
  *  $page->getPagesFromDateRange('date1', '2019/01/01', '2019/03/11', 53);
  */
  public function getPagesFromDateRange($event) {
    $page = $event->object;
    $fieldname = $event->arguments(0);
    $dateStart = $this->sanitizer->datetime($event->arguments(1));
    $dateEnd = $this->sanitizer->datetime($event->arguments(2));
    $templateId = $this->sanitizer->int($event->arguments(3));

    $q = new DatabaseQuerySelect();
    $q->select("pages.id");
    $q->from("pages");
    $q->where("
      (pages.parent_id IN (SELECT pages_id FROM pages_parents WHERE parents_id={$page->id})) 
      AND (pages.templates_id={$templateId}) 
      AND(
      pages.id IN (
        SELECT pages.id 
        FROM `pages` 
        JOIN field_{$fieldname} AS dateStart ON dateStart.pages_id=pages.id AND (((DATE(dateStart.data)>='{$dateStart}'))) 
        JOIN field_{$fieldname} AS dateEnd ON dateEnd.pages_id=pages.id AND (((DATE(dateEnd.data)<='{$dateEnd}'))) 
      )   
    )");

    $stmt = $q->execute();
    $event->return = $stmt->fetchAll(\PDO::FETCH_ASSOC);
  }
}

 

 

 

 

 

Edited March 12, 2019 by flydev
code typo

[Robin S]

  On 3/11/2019 at 6:09 PM, flydev said:

$templateId = $this->sanitizer->datetime($event->arguments(3));

Expand  

Should this have been $this->sanitizer->int() ?

It would be awesome to be also able to pass a selector string to the method (e.g. "some_field%=value") but I guess it would get quite involved to translate that into SQL.

[Elchin]

  On 3/11/2019 at 5:39 PM, Autofahrn said:

ok, tested a little and the selectors seem to work for me, primarily tested with this one:

$results = $page->children("!date_end|date_end>=$start,date<$start,sort=-date,limit=12");

Did you try outputting your date and date_end fields unformatted to see if they contain proper values?

Expand  

This not work for me.

The example below returns pages that have property `date_end` and `date_end`>=$start

$results = $page->children("date_end>=$start,date<$start,sort=-date,limit=12");

The example below returns pages that not have property `date_end` at all

$results = $page->children("!date_end,date<$start,sort=-date,limit=12");

But I can't get this pages with this two conditions together.

I don't know why but the OR condition `!date_end|date_end>=$start` didn't worked in this case.

  On 3/11/2019 at 6:09 PM, flydev said:

Hi @Elchin

I got them in the past but the query was to slow so I finally used a custom query to achieve that, you might be interested as I don't know the number of pages your trying to fetch - I give you a small module which could be extended, shipped with a hook function which return all pages between two dates given a fieldname and a template id :

 

  Reveal hidden contents

<?php namespace ProcessWire;


class MyPageHooks extends WireData implements Module {

  public static function getModuleInfo() {

      return array(
          'title'    => 'MyPageHooks',
          'version'  => '1.0.0',
          'summary'  => 'Adds new methods to $page',
          'singular' => true,
          'autoload' => true,
          'author'   => 'flydev',
          'href'     => 'https://processwire.com/talk/topic/21021-selector-where-now-between-start-date-and-end-date-or-now-bigger-than-start-date-and-end-date-is-empty/'
      );
  }

  public function init() {
    $this->addHook('Page::getPagesFromDateRange', $this, 'getPagesFromDateRange');
  }

  /**
  * Hook getPagesFromDateRange(string fieldName, string $dateStart, string $dateEnd, int $templateId)
  * date format: YYYY/MM/DD
  * 
  * Usage:
  * 
  * 
  *  Get pages between `dateStart` and `dateEnd` with template id 53 from fieldname 'date1'
  *  $page->getPagesFromDateRange('date1', '2019/01/01', '2019/03/11', 53);
  */
  public function getPagesFromDateRange($event) {
    $page = $event->object;
    $fieldname = $event->arguments(0);
    $dateStart = $this->sanitizer->datetime($event->arguments(1));
    $dateEnd = $this->sanitizer->datetime($event->arguments(2));
    $templateId = $this->sanitizer->datetime($event->arguments(3));

    $q = new DatabaseQuerySelect();
    $q->select("pages.id");
    $q->from("pages");
    $q->where("
      (pages.parent_id IN (SELECT pages_id FROM pages_parents WHERE parents_id={$page->id})) 
      AND (pages.templates_id={$templateId}) 
      AND(
      pages.id IN (
        SELECT pages.id 
        FROM `pages` 
        JOIN field_{$fieldname} AS dateStart ON dateStart.pages_id=pages.id AND (((DATE(dateStart.data)>='{$dateStart}'))) 
        JOIN field_{$fieldname} AS dateEnd ON dateEnd.pages_id=pages.id AND (((DATE(dateEnd.data)<='{$dateEnd}'))) 
      )   
    )");

    $stmt = $q->execute();
    $event->return = $stmt->fetchAll(\PDO::FETCH_ASSOC);
  }
}

 

 

 

 

 

Expand  

How can I use paging in this case?

[Robin S]

@Elchin, try:

$results = $page->children("date<today, (date_end>today), (date_end=''), sort=-date, limit=12");

You can use "today" in place of your timestamp.

[Elchin]

  On 3/12/2019 at 7:12 AM, Robin S said:

@Elchin, try:

$results = $page->children("date<today, (date_end>today), (date_end=''), sort=-date, limit=12");

You can use "today" in place of your timestamp.

Expand  

Thank you @Robin S

This is working. Please explain why my variants not works and why your variant works?

[Robin S]

  On 3/12/2019 at 7:17 AM, Elchin said:

Please explain why my variants not works and why your variant works?

Expand  

 

  On 3/11/2019 at 2:24 PM, Elchin said:

$results = $page->children("foo=(date<$start,date_end=''),bar=(date<$start,date_end>=$start),sort=-date,limit=12");

Expand  

The naming of the OR-groups is not correct. You only name OR-groups if there is more than one set of OR-groups and one group in each named set must match. Your selector here is saying that both (date<$start,date_end='') and (date<$start,date_end>=$start) must match which is impossible because these contradict each other.

  On 3/11/2019 at 2:24 PM, Elchin said:

results = $page->children("date<$start,(date_end='',date_end>=$start),sort=-date,limit=12");

Expand  

Wrong use of OR-group syntax with only one OR condition.

  On 3/11/2019 at 2:24 PM, Elchin said:

$results = $page->children("date_end=''|date_end>=$start,date<$start,sort=-date,limit=12");

Expand  

  On 3/11/2019 at 2:24 PM, Elchin said:

$results = $page->children("!date_end|date_end>=$start,date<$start,sort=-date,limit=12");

Expand  

  On 3/11/2019 at 2:24 PM, Elchin said:

$results = $page->children("date_end>=$start|!date_end,date<$start,sort=-date,limit=12");

Expand  

Wrong use of pipe character. The pipe character can only go between field names in a selector clause...

field_a|field_b~=foo

...or between values in a selector clause...

field_a%=foo|bar

 

[Elchin]

Thank you @Robin S very much.

[flydev]

  On 3/12/2019 at 6:03 AM, Elchin said:

How can I use paging in this case?

Expand  

 

To paginate the result, you have to know the total amount of pages you fetch so we must add another hook to our module (full code here) :

 

  Reveal hidden contents

<?php namespace ProcessWire;


class MyPageHooks extends WireData implements Module {

  public static function getModuleInfo() {

      return array(
          'title'    => 'MyPageHooks',
          'version'  => '1.0.0',
          'summary'  => 'Adds new methods to $page',
          'singular' => true,
          'autoload' => true,
          'author'   => 'flydev',
          'href'     => 'https://processwire.com/talk/topic/21021-selector-where-now-between-start-date-and-end-date-or-now-bigger-than-start-date-and-end-date-is-empty/'
      );
  }

  public function init() {
    $this->addHook('Page::getPagesFromDateRange', $this, 'getPagesFromDateRange');
    $this->addHook('Page::getPagesCountFromDateRange', $this, 'getPagesCountFromDateRange');
  }

  /**
  * Hook getPagesFromDateRange(string fieldName, string $dateStart, string $dateEnd, int $templateId)
  * date format: YYYY/MM/DD
  * 
  * Usage:
  * 
  *  Get pages between `dateStart` and `dateEnd`
  *  $result->getPagesFromDateRange('date1', '2019/01/01', '2019/03/11', 53);
  */
  public function getPagesFromDateRange($event) {
    $page = $event->object;
    $fieldname = $event->arguments(0);
    $dateStart = $this->sanitizer->datetime($event->arguments(1));
    $dateEnd = $this->sanitizer->datetime($event->arguments(2));
    $templateId = $this->sanitizer->int($event->arguments(3));
    $start = $this->sanitizer->int($event->arguments(4));
    $perPage = $this->sanitizer->int($event->arguments(5));

    $q = new DatabaseQuerySelect();
    $q->select("pages.id");
    $q->from("pages");
    $q->where("
      (pages.parent_id IN (SELECT pages_id FROM pages_parents WHERE parents_id={$page->id})) 
      AND (pages.templates_id={$templateId}) 
      AND(
      pages.id IN (
        SELECT pages.id 
        FROM `pages` 
        JOIN field_{$fieldname} AS dateStart ON dateStart.pages_id=pages.id AND (((DATE(dateStart.data)>='{$dateStart}'))) 
        JOIN field_{$fieldname} AS dateEnd ON dateEnd.pages_id=pages.id AND (((DATE(dateEnd.data)<='{$dateEnd}'))) 
        )   
     ) ORDER BY pages.id DESC LIMIT {$start},{$perPage}");

    $stmt = $q->execute();
    $event->return = $stmt->fetchAll(\PDO::FETCH_ASSOC);
  }

  /**
  * Hook getPagesCountFromDateRange(string fieldName, string $dateStart, string $dateEnd, int $templateId)
  * date format: YYYY/MM/DD
  * 
  * Usage:
  * 
  *  Get pages count between `dateStart` and `dateEnd`
  *  $count = $result->getPagesFromDateRange('date1', '2019/01/01', '2019/03/11', 53);
  *  echo $count['total'];
  */
  public function getPagesCountFromDateRange($event)
  {
    $page = $event->object;
    $fieldname = $event->arguments(0);
    $dateStart = $this->sanitizer->datetime($event->arguments(1));
    $dateEnd = $this->sanitizer->datetime($event->arguments(2));
    $templateId = $this->sanitizer->int($event->arguments(3));

    $q = new DatabaseQuerySelect();
    $q->select("count(pages.id) as total");
    $q->from("pages");
    $q->where("
      (pages.parent_id IN (SELECT pages_id FROM pages_parents WHERE parents_id={$page->id})) 
      AND (pages.templates_id={$templateId}) 
      AND(
      pages.id IN (
        SELECT pages.id 
        FROM `pages` 
        JOIN field_{$fieldname} AS dateStart ON dateStart.pages_id=pages.id AND (((DATE(dateStart.data)>='{$dateStart}'))) 
        JOIN field_{$fieldname} AS dateEnd ON dateEnd.pages_id=pages.id AND (((DATE(dateEnd.data)<='{$dateEnd}'))) 
        )   
     ) ORDER BY pages.id");

    $stmt = $q->execute();
    $total = $stmt->fetchAll(\PDO::FETCH_ASSOC);

    $event->return = $total[0];
  }
}

 

 

 

Then you just have to write a little script to paginate the result. To try it, create on you webroot directory a file called `test.php` and put the following code in then navigate to hxxp://example.com/test.php :

 

  Reveal hidden contents

<?php namespace ProcessWire;
// boostrap processwire for the test page
include_once './index.php';

$count = 0;
$limit = 20;
$page = (isset($_GET['page'])) ? (int)$_GET['page'] : 1;
$start = $limit * ($page - 1);

// parent page
$p = wire('pages')->get('/parkings/cesh/');
// get our result with a LIMIT of number of pages
$r = $p->getPagesFromDateRange('date_sortie', '2019/03/01', '2019/03/11', 53, $start, $limit);
// get the total count of children with template id 53 if not set
$count = $p->getPagesCountFromDateRange('date_sortie', '2019/03/01', '2019/03/11', 53); 
// get the number of pages in the 
$totalPages = ceil($count['total'] / $limit);

// the result table per paginated pages
echo "<table border='1'><tr><th>id</th><th>title</th></tr>";
if(count($r)) {
  foreach ($r as $row) {
    echo "<tr><td>". $row['id'] ."</td><td>". $wire->pages->get($row['id'])->title ."</td>";
  }
}
echo "</table>";

// pagination links
$pagination = "";
for($i = 1; $i <= $totalPages; $i++) {
  $pagination .= ($i !== $page)
    ? "<a href='?page=$i'>Page $i</a> "
    : "$page ";
}
echo $pagination; 

 

 

Result :

 

 

  On 3/12/2019 at 5:51 AM, Robin S said:

Should this have been $this->sanitizer->int() ?

Expand  

correct, corrected - a bad copy pasta moment ?

[Elchin]

Thanks @flydev