Custom Query: Counting number of pages added after a certain date

Permalink
Hey everyone,

I have a page (cID 81) that is a parent to 100+ pages. I want to be able to run a query that returns the ids of any approved sub-page of 81 that has a creation date on or after the user's last login. So basically I can say "5 new articles were added since your last login: " and then list them out as links.

I see that the Collections table has 'cDateAdded' and the users table has 'uLastLogin' but I'm unsure where the link exists that assigns a Collection to a parent (as I only want to join in any approved page with the parent id of 81).

Thanks for any help!

 
jordanlev replied on at Permalink Best Answer Reply
jordanlev
I'd recommend using the "Page List" API to perform this query instead of writing a SQL statement. Something like this should work:
//Retrieve user's last login date
$u = new User();
$ui = UserInfo::getByID($u->getUserID());
$lastLogin = $ui->getLastLogin(); //<--if this doesn't work, try $ui->getPreviousLogin();
//Retrieve parent page path
$parentCID = 81;
$parentPage = Page::getByID($parentCID);
$parentPath = $parentPage->getCollectionPath();
//Retrieve pages under parent page that have been added since user's last login
Loader::model('page_list');
$pl = new PageList
$pl->filterByPath($parentPath);
$pl->filterByPublicDate(date('Y-m-d', $lastLogin), '>=');
//$pages = $pl->get(); //<--uncomment this if you want the pages themselves (not just the count)
$pageCount = $pl->getTotal();
Flight643 replied on at Permalink Reply
Thank you! That worked amazingly!
chris123uk replied on at Permalink Reply
chris123uk
AWESOME. THANKS FOR SHARING :o