Jump to content
Elchin

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

Recommended Posts

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.

Share this post


Link to post
Share on other sites

Guess you have to compare timestamps not the formatted values:

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

Share this post


Link to post
Share on other sites

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?

  • Like 1

Share this post


Link to post
Share on other sites

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 by flydev
code typo
  • Like 4

Share this post


Link to post
Share on other sites
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.

Share this post


Link to post
Share on other sites
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?

Share this post


Link to post
Share on other sites

@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.

  • Like 1

Share this post


Link to post
Share on other sites
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?

Share this post


Link to post
Share on other sites
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

 

  • Like 5

Share this post


Link to post
Share on other sites
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 :

pagination.gif

 

 

3 hours ago, Robin S said:

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

correct, corrected - a bad copy pasta moment 🙂

Share this post


Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now

  • Recently Browsing   0 members

    No registered users viewing this page.

  • Similar Content

    • By Roberts R
      I have simple page structure:
      category subcategory simple-product simple-product simple-product simple-product subcategory .... category2 so Im at category page and running
      $pages->find("template=simple-product, has_parent=$page, limit=8"); and it returns 0 pages
       
      but this returns 4 pages
      $pages->find("template=simple-product, has_parent=$page"); Can someone explain why limit does not work here?
      EDIT:
      So I did some tests and it seems that any limit=n where n is >= actual page count that is possible ... selector return 0 results.
    • By jds43
      Hello, I have a Page Reference by template radio button field to promote a certain page (only two options), but I'd like to target the page that isn't selected. This would be used dynamically throughout the site.
      Does anyone know how I could accomplish this? Would I use something like remove() or not()?
       
    • By Hubris
      Hi there!
      I'm using some page reference fields to create lists of tags, categories, years, etc.. I'm able to find the pages like so:
      $pages->find("template=project, {$filter}={$page->title}"); Which dynamically does something like: 
      $pages->find("template=project, tags=Experimental"); Only if the value (the page name, like "Experimental") starts with letters. If it starts with numbers, find returns nothing.
      Why is this and how can I fix it?
    • By MateThemes
      Hello everyone.
      I have a question that i can't find a way to solve.
      I have following function in _uikit.php 
      $date = $page->get('date|createdStr'); $dateModified = $page->get('datemodified'); But I need to output the $date in to different formats.
      My further function looks like this
      // return the blog post article markup return " <div> <article class='uk-article blog-post $class'> <meta property='name' content='$page->title'> <meta property='author' typeof='Person' content='Arra Lifte Harmanschlag'> <meta property='dateModified' content='$dateModified'> <meta property='datePublished' content='$date'> <meta class='uk-margin-remove-adjacent' property='articleSection' content='News'> <div property='image' typeof='ImageObject'> $featuredBlogPostImage </div> $heading <ul class='mt25 uk-margin-remove-bottom uk-subnav uk-subnav-divider'> <li class='uk-article-meta'> <time datetime='$date'>$byline</time> </li> </ul> <div class='mt25' property='text'> $body </div> </article> </div> "; Now I need to output the meta property in this format 2019-03-02CET05:23:00 and then a normal date format that is displayed on the Homepage with 2. März 2019 without time.
      Can anybody help me?
      Thanks in advance.
    • By pwFoo
      I played with the Selectors object and would like to use it for a special use case...
      That is a custom Selectors object with dummy data converted to an php array to see the structure (Selectors object is a WireArray with "fields" added)
      Array ( [0] => Array ( [0] => Array ( [field] => seg1 [value] => val1 [not] => [group] => [quote] => [forceMatch] => ) [1] => Array ( [field] => seg2 [value] => val2 [not] => [group] => [quote] => [forceMatch] => ) ) )  
      But instead of "seg1" the field name is "field" with value "seg1" (= my field name).
       
      So I can't search the Selectors WireArray (= custom WireArray with added Selectors objects) with PW "find('seg1=val1')", Is there a way to search with "find()" or build a simple wrapper to make the elements searchable / filterable with find()?
×
×
  • Create New...