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 :

 

Spoiler

<?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]

11 hours ago, flydev said:

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

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]

12 hours ago, 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?

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.

11 hours ago, 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);
  }
}

 

 

 

 

 

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]

4 minutes ago, 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.

Thank you @Robin S

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

[Robin S]

2 minutes ago, Elchin said:

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

 

16 hours ago, Elchin said:

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

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.

16 hours ago, Elchin said:

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

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

16 hours ago, Elchin said:

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

16 hours ago, Elchin said:

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

16 hours ago, Elchin said:

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

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]

2 hours ago, Elchin said:

How can I use paging in this case?

 

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) :

 

Spoiler

<?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 :

 

Spoiler

<?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 :

 

 

3 hours ago, Robin S said:

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

correct, corrected - a bad copy pasta moment ?

[Elchin]

Thanks @flydev