Get all pages where a field is not set

[pogidude]

I have an announcement template with the following date fields:

- publish_date

- expire_date

The idea is to show announcements that should be be published by now (exclude pages whose publish date is still in future) and whose expire_date is not yet due. Also, if the expire_date is not filled out, then it means the page has no expiration.

That last condition is what's stumping me. If all pages has both publish_date and expire_date then no problem since we can do a find like this:

$announcements = $page->children("expire_date>$now, publish_date<=$now");

where $page in this case is the parent for all announcements. Oh yeah, that line was taken from this thread. Thanks!

Unfortunately, that snippet won't return pages with empty expire_date

My solution is this:

$now = time();
$all = $page->children("date<=$now,limit=20");

//get all expired announcements
$expired = clone $all;
$expired->filter("expiration_date<$now,expiration_date>0");

//remove all expired announcements
//$announcements = $all->remove($expired);
$all->filter("id!={$expired}");

unfortunately, this messes up paging in some way.

This is my full code:

function renderAnnouncements($page){
   $now = time();
   $all = $page->children("date<=$now,expiration_date>$now");

   //get all expired announcements
   $expired = clone $all;
   $expired->filter("expiration_date<$now,expiration_date>0");

   //remove all expired announcements
   //$announcements = $all->remove($expired);
   //$all->filter("id!={$expired}");

   foreach($all as $p){
      if(empty($p->summary)) {
         // summary is blank so we auto-generate a summary from the body
         $summary = strip_tags(substr($p->body, 0, 450));
         $p->summary = substr($summary, 0, strrpos($summary, ' '));
      }
   }

   $t = new TemplateFile(wire('config')->paths->templates . '/markup/announcements.php'); 
   $t->set('announcements', $all); 
   $out = $t->render();

   // if there are more annoucements than the specified limit, then output pagination
   if($all->getLimit() < $all->getTotal()) $out .= $all->renderPager(); 
   
   return $out;   
}

[ryan]

This is a little bit of a tough one because dates are basically sortable integers. So a 0 is considered end of 1969 (or somewhere around there). You can query for those with a 0 date value and those with a date>$now with separate children() calls. But when it comes to pagination, the question becomes how you would want to sort those with no date value and those with a date value. If one or the other can be consistent at the front or back, that will make pagination simpler. Also you can manually set the pagination limits on a PageArray with $all->setLimit(); $all->setTotal(); etc.

Another option is that you could always query the database manually to find the IDs of the matching pages, then bundle that into your children() query. Here's two example, one using PDO (PW 2.3.1 and newer) and one using mysql (PW 2.3.0 and earlier): 

// using PDO
$now = date('Y-m-d H:i:s'); 
$sql = 'SELECT pages_id FROM field_expiration_date WHERE (data IS NULL OR data=0 OR data>:now)';
$query = $database->prepare($sql);
$query->bindValue(':now', $now); 
$query->execute();
$ids = $query->fetchAll(PDO::FETCH_COLUMN, 0);  
$expired = $page->children("limit=20, date<=$now, id=" . implode('|', $ids)); 

// using mysqli
$now = date('Y-m-d H:i:s'); 
$sql = "SELECT pages_id FROM field_expiration_date WHERE (data IS NULL OR data=0 OR data>'$now')";
$result = $db->query($sql); 
$ids = array();
while($row = $result->fetch_row()) $ids[] = list($row); 
$expired = $page->children("limit=20, date<=$now, id=" . implode('|', $ids)); 

[pogidude]

Hi Ryan, this is basically the kind of "selector" I was looking for. An OR selector something that looks like this: $page->children("date<=$now|date=0"); I guess will have to do a manual query for something like this like you said. Thanks!

The paging functions you mentioned are interesting. Still not very sure how the pager is created here.. will have a separate thread of questions for those

[onjegolders]

Just picking up this old thread as I'm having issues with my selector when trying to only show pages whose date_field either is in the future or it is empty. Without putting all the various combinations on this thread. Does anyone have a working example?
 

I was using

$today = time(); 

and then checking that against my field "end_date".

I've used children, find, filter, remove etc.

I can't seem to find the combination where empty date fields also play nice.

Thanks guys.

[pogidude]

What you'll probably want to do is:

1. Find all pages whose date_field is not empty but whose date_field is past $today.

$what_you_dont_want = $this->pages->find("date_field>0, date_field<$today");

2. now select what you want

$what_you_want = $this->pages->find("template=template_with_date_field, id!=$what_you_dont_want, limit=$limit");

The general idea is:

1. Find all pages that you want to exclude.

2. Find ALL the pages but filter out the pages found in #1.

Thoughts:

You can use filter() method for this by finding all the pages you need first then filtering out what you don't want but this somehow messes up pagination by default as what I mentioned in the original post. BUT, ryan did say you can use setTotal() and setLimit() methods on the PageArray but I haven't tried it since I'd already found a solution but then was too lazy to rewrite

[onjegolders]

Thanks Pogidude, that's really helpful, I will try and do it this way around.