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 Peter Knight
      Hey I'm building my first new site in well over a year and am a little rusty on selectors but particularly retrieving sub-fields of selected pages.
      I am trying to output the meta data of a blog post as follows.
      [Person Name] is just a field with a Page Reference and simple enough.
      [Job Title] is the sub-field within the page that was referenced above.
      I actually have it working with the following:
      Posted by: <?php if($page->insight_author) { echo $page->insight_author("<a href='{url}'>{title}</a>");} ?> , <?php $roles = $page->insight_author; foreach ($roles as $role) { echo "{$role->staff_role}";} ?> but was wondering how to do this with selector sub-selectors instead. My current code is probably quite 'old school'?
      Thanks
       
    • By theoretic
      Hi there! And thanks for Processwire!
      I have an interesting task which i cannot fulfill as i want. Maybe someone could help me please?
      Let's imagine a simple page structure of this kind:
      Category 1
      + Item 1.1
      + Item 1.2
      Category 2
      + Item 2.1
      + Item 2.2
      My task is to attach some items to more than one category, at least to show some items on different frontend category pages. With PW, it's a piece of cake. I've just created a field called Items (of type Page Reference) and attrached it to Category template. Since i have lots of items inside each category i preferred to use Page Autocomplete input for my Items field. The pages available for autocomplete are restricted by a very simple selector:
      template=item
      It works like a charm. But later i decided to make this autocomplete even smarter and to exclude current category children items from it. I tried to update my selector this way...
      template=item,parent!=(page)
      ...and oops, this broke my selector. My autocomplete founds nothing. Sorry, i had to replace the square braces by () because of this forum limitations, i swear i'm using square brackets in real-life selector!
      What am i doing wrong? And is there any way to include current page info in autocomplete-related selectors? Thanks in advance!
       
    • By Robin S
      Lister Selector
      A Process module that uses Lister/ListerPro, but with a selector string input instead of the normal InputfieldSelector filters.
      Features
      For power users, typing a selector string is often faster and more intuitive than fiddling with InputfieldSelector. It also lets you copy/paste selector strings that you might be using somewhere else in your code.
      Allows the Lister rows to be sorted by multiple fields (not possible in Lister/ListerPro)
      Allows the use of OR-groups (not possible in Lister/ListerPro)
      If ListerPro is installed you can run ListerPro actions on the listed pages - the available actions are defined in the module config.
      Bookmarks can be configured in the module config and accessed via the flyout menu for the module page.
      Usage
      Type your selector string on the Selector tab. The selector is applied when the "Selector string" field is blurred, so hit Tab when you have finished typing your selector.
      Unlike Lister/ListerPro, you can't sort results by clicking the column headings. Control the sort within the selector string instead.
      Superusers can jump to the module config (e.g. to create a bookmark) by clicking the cog icon at the top right of the module interface.
      The module is mostly intended for use by superusers, because in most cases site editors won't understand the ProcessWire selector string syntax. If you want another role to be able to access Lister Selector then give the role the "lister-selector" permission. Only superusers can define bookmarks because in ProcessWire module config screens are only accessible to superusers.
      Screenshots
      Process page

      Module config (when ListerPro is installed)

      Advanced
      If for any reason you want to create dynamic bookmark links to Lister Selector for a given selector you can do that like this:
      /** @var $pls ProcessListerSelector */ $pls = $modules->get('ProcessListerSelector'); $selector = "template=foo, title%=bar"; $pls_link = $pls->getProcessPage()->url . '?bm=' . $pls->urlSafeBase64Encode($selector); echo "<a href='$pls_link'>My link</a>";  
      https://github.com/Toutouwai/ProcessListerSelector
      https://modules.processwire.com/modules/process-lister-selector/
    • By ottogal
      Hello all,
      using PW 3.0.148 with the regular site profile for a blog, I got an an empty pagination output when I had a Toggle field in the selector.
      The Toggle Fieldtype was introduced with https://processwire.com/blog/posts/pw-3.0.139/ .
      The selector resulting in empty pagination:
      $posts = $pages->find("parent=blog, sort=-date, limit=10, toggle_field=0"); It worked well, when I replaced the Toggle field with a Checkbox field:
      $posts = $pages->find("parent=blog, sort=-date, limit=10, checkbox_field=0"); So the prerequisites for the pagination to work are given.
      The settings for the Toggle field were:
      Formatted value: Integer Label Type: Yes/No Input Type: Toggle buttons Default selected option: No Thanks for any hints!
    • By snck
      Hi there,
      I have a problem constructing a selector that finds all pages that refer to pages with a specific template.
      I have pages using an event template and I want to show events based on a specific context. In this example I want to filter the results and only show event pages that relate to a specific template (exhibitions) in their page field related_pages.
      What I tried:
      $events = $pages->find("template=event, related_pages.template.name=exhibition"); Unfortunately it does not work (0 results).
      Same with this:
      $events = $pages->find("template=event, related_pages=[template.name=exhibition]"); At the moment I am helping myself with the following lines, but I have a strong feeling that there is a more efficient solution:
      $events = $pages->find("template=event"); foreach($events as $event){ if(!count($event->related_pages->find("template=exhibition"))){ $events->remove($event); } }  
      I really hope that one of you can help me out.
      Thanks in advance!
      Flo
×
×
  • Create New...