"Manipulating the WHERE" - Advice on getting this to work?

[matt-bailey]

I'm using DataObject-as-Page to create a blog and I have a db table called BlogPost. In this table there is a row called BlogPostPublicationDate.

I'm trying to filter the Items loop so that it won't show posts that have a publication date in the future. I thought this would work (based on your example here), but I get an error:

class BlogHolder_Controller extends DataObjectAsPageHolder_Controller
{
// $item_class, $item_sort etc.
    public function getItemsWhere()
    {
        return "BlogPostPublicationDate <= CURDATE";
    }

[WARNING] INVALID ARGUMENT SUPPLIED FOR FOREACH()

I've also tried it with your exact example return "Date > CURDATE";, but that also throws the same error. Do you have any suggestions that could point me in the right direction?

[matt-bailey]

I've also tried return "Created > CURDATE";. Same error.

Reading on a bit in the instructions I wondered if I needed to use getItemsJoin to join my BlogPost table to the DataObjectAsPage. Again, this seemed to make sense, but that also errors.

    function getItemsJoin()
    {
        return "INNER JOIN BlogPost ON DataObjectAsPage.ID = BlogPost.ID";
    }

    public function getItemsWhere()
    {
        return "BlogPostPublicationDate <= CURDATE";
    }

[USER ERROR] COULDN'T RUN QUERY: SELECT DISTINCT "DATAOBJECTASPAGE_LIVE"."CLASSNAME"...
// etc.

I'm clearly not understanding how this should work - are you able to help?

[aledbrown]

Hi Matt

I'm sure I've posted a worked example of how to do this on the issues pages. I'll track it down when I'm at my computer later and send you a link.

You need to use an array of filters as per how SS3 does it. It's not easy without an example though. I spent hours making it work.

Something about filtering was the issue title.

Best regards Aled

On 2 Aug 2014, at 17:11, Matt Bailey notifications@github.com wrote:

I've also tried return "Created > CURDATE";. Same error.

Reading on a bit in the instructions I wondered if I needed to use getItemsJoin to join my BlogPost table to the DataObjectAsPage. Again, this seemed to make sense, but that also errors.

function getItemsJoin()
{
    return "INNER JOIN BlogPost ON DataObjectAsPage.ID = BlogPost.ID";
}

public function getItemsWhere()
{
    return "BlogPostPublicationDate <= CURDATE";
}

I'm clearly not understanding how this should work - are you able to help?

— Reply to this email directly or view it on GitHub.

[aledbrown]

I can't find what I thought I'd posted so I'll do a new version.

It was when 3.1 came out with the new filtering that things changed: https://github.com/arambalakjian/DataObject-as-Page/pull/46

Here's an example for a news page holder sub-class page:

public function getItemsWhere()
{
    //Old 3.0 filter
    //return "EmbargoDate <= CURDATE() AND EmbargoDate <= ExpiryDate";

    $dateTomorrow = date("Y-m-d", time()+86400);
    $dateToday = date("Y-m-d");

    // New 3.1 filter
    return array('filter' => array (
        'EmbargoDate:LessThan' => $dateTomorrow,
        'ExpiryDate:GreaterThan' => $dateToday
    ));

}

Using that format you can use Field names on both sides too.

You can use all the new LessThanOrEqual from SS3 if you're on the latest 3.1.5. Some of those key word things only started working in newer versions.

Hope that helps. Aled

[matt-bailey]

Wow, thanks so much Aled! I'm away for a couple of weeks, but I'll give this a go as soon as I'm back.

On Saturday, August 2, 2014, Aled Brown notifications@github.com wrote:

I can't find what I thought I'd posted so I'll do a new version.

It was when 3.1 came out with the new filtering that things changed:

46 https://github.com/arambalakjian/DataObject-as-Page/pull/46

Here's an example for a news page holder sub-class page:

public function getItemsWhere() { //Old 3.0 filter //return "EmbargoDate <= CURDATE() AND EmbargoDate <= ExpiryDate";

$dateTomorrow = date("Y-m-d", time()+86400);
$dateToday = date("Y-m-d");

// New 3.1 filter
return array('filter' => array (
    'EmbargoDate:LessThan' => $dateTomorrow,
    'ExpiryDate:GreaterThan' => $dateToday
));

}

Using that format you can use Field names on both sides too.

You can use all the new LessThanOrEqual from SS3 if you're on the latest 3.1.5. Some of those key word things only started working in newer versions.

Hope that helps. Aled

— Reply to this email directly or view it on GitHub https://github.com/arambalakjian/DataObject-as-Page/issues/56#issuecomment-50971526 .

---

Matt Bailey Designer and web developer

http://www.mattbailey.co +44 (0)7769 700 733

[matt-bailey]

@aledbrown I just wanted to say thanks a million for your help, it was exactly what I needed and works perfectly. I'm only using the 'LessThan' filter, but I like the way you've shown how to filter by expiry date as well.

For completeness here is my code:

public function getItemsWhere()
{
    $dateTomorrow = date("Y-m-d", time()+86400);

    return array('filter' => array (
        'BlogPostPublicationDate:LessThan' => $dateTomorrow
    ));
}