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 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
    • By Kiwi Chris
      The selector in the following code included in a template is returning nothing, however if I take out the compId.resultsdate<={$today} bit, it works fine, although obviously not filtered on the date field.
      $today = strtotime(date('Y-m-d')); $setImages = $pages->find("template=competitionImage, compId={$page->id}, compId.resultsdate<={$today}, compSubject.name=s, imageRating.title=Merit|Honours,check_access=0"); Here's the results of an example from Tracey Debugger
      templates_id=79, resultsdate<=1587729600, status<2048   SELECT pages.id,pages.parent_id,pages.templates_id FROM `pages` JOIN field_resultsdate AS field_resultsdate ON field_resultsdate.pages_id=pages.id AND (((field_resultsdate.data<='2020-04-25 00:00:00' ) )) WHERE (pages.templates_id=79) AND (pages.status<2048) GROUP BY pages.id Over in my ready.php I have inside a hook that refers directly to the page template that's used for the pages in the page field above:
      $today = strtotime(date('Y-m-d')) $event->return = $event->pages->find("template=competition,eventEnd>={$today},eventStart<={$today}"); In this case the filtering on date fields (albeit different ones) works fine. Can anyone suggest why the filter on the date subfield of the page field isn't working?
      Just to confirm, I do have a date value in the field, and it is a date before today. 🙂
      The problem may be something blatantly obvious, but I can't for the life of me figure out why the selector is returning no results when I include the date filter.
    • 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?
×
×
  • Create New...