Forum

Thread tagged as: Question, Problem, Blog

Filtering with perch_blog_custom

I'm trying to set up a blog (using the blog app) that can be used to post blog-posts and posts about upcoming events.

On the blog list page, I want upcoming events to show up first, followed by everything else - regular posts and past events.

To do this I've created two extra fields:

<perch:blog id="blogEventCheck" type="checkbox" label="This is an event" value="1" />
<perch:blog id="blogEventDate" type="date" label="Event start date" format="Y-m-d" />

(I'm not using categories for events as they are used for another purpose already)

I then want to have upcoming events show up at the top of the list of blog posts until their event date is passed, at which point they revert back to their position in the list.

I've been trying to do this with:

        perch_blog_custom(array(
            'filter'=>array(
                'filter'=>'blogEventCheck',
                'match'=>'eq',
                'value'=>1
            ),
            array(
                'filter'=>'blogEventDate',
                'match'=>'gte',
                'value'=>date('Y-m-d')
            ),
        ),
            'count' => 10,
            'template' => 'post_in_list.html',
            'sort' => 'postDateTime',
            'sort-order' => 'DESC',
        ));

to display the upcoming events only and then:

        perch_blog_custom(array(
            'filter'=>array(
                'filter'=>'blogEventCheck',
                'match'=>'neq',
                'value'=>1
            ),
            array(
                'filter'=>'blogEventDate',
                'match'=>'lte',
                'value'=>date('Y-m-d')
            ),
        ),
            'count' => 10,
            'template' => 'post_in_list.html',
            'sort' => 'postDateTime',
            'sort-order' => 'DESC',
        ));

to display everything else - regular posts and past events.

It doesn't appear to work at all. Where am I going wrong, and is there an easier way to achieve what I'm after?
Alex Magill

Alex Magill 0 points

  • 7 years ago
Drew McLellan

Drew McLellan 2638 points
Perch Support

7 years ago 
perch_blog_custom(array(
    'filter' => array(
        array(
            'filter'=>'blogEventCheck',
            'match'=>'eq',
            'value'=>1
        ),
        array(
            'filter'=>'blogEventDate',
            'match'=>'gte',
            'value'=>date('Y-m-d')
        ),
    ),
    'count' => 10,
    'template' => 'post_in_list.html',
    'sort' => 'postDateTime',
    'sort-order' => 'DESC',
));
Alex Magill

Alex Magill 0 points

7 years ago

Hi Drew, thanks for the quick reply, however, that doesn't seem to work. It returns a list of posts, ignoring the filters completely.

I've just tried a slightly more simple approach, sorting by blogEventDate:

perch_blog_custom(array(
    'count' => 10,
    'template' => 'post_in_list.html',
    'sort' => 'blogEventDate',
    'sort-order' => 'DESC',
));

My thinking was that, as the date is automatically populated, non-event posts will get today's date, events will get a future date.

Unfortunately this gets no results (empty array).

Is it a limitation of the blog app that you can't filter or sort on non-standard fields or have I done something odd somewhere?

Thanks, Alex
Drew McLellan

Drew McLellan 2638 points
Perch Support

7 years ago

Are you on the latest version of Blog? If not, it doesn't support multiple filters.
Alex Magill

Alex Magill 0 points

7 years ago

Yep, fully upgraded. I can't get single filters, multiple filters or sorts to work on non-standard blog fields (ie, ones I've created).
Drew McLellan

Drew McLellan 2638 points
Perch Support

7 years ago

Can you turn on debug for the page and let me know what it outputs?
Alex Magill

Alex Magill 0 points

7 years ago

For that last query - sorting on the blogEventDate field - it outputs the error:

Invalid query: SQLSTATE[42S22]: Column not found: 1054 Unknown column 'blogEventDate' in 'order clause'
Drew McLellan

Drew McLellan 2638 points
Perch Support

7 years ago

Can you show me the entire output? The error alone isn't enough to help.
Alex Magill

Alex Magill 0 points

7 years ago

The complete output is:

DIAGNOSTICS:
SELECT regionKey, regionHTML FROM perch2_content_regions 
WHERE regionPage='/site/news-events/index.php' OR regionPage='*' ORDER BY regionPage DESC
SELECT DISTINCT settingID, settingValue FROM perch2_settings WHERE userID=0
SELECT SQL_CALC_FOUND_ROWS DISTINCT p.* FROM perch2_blog_posts p WHERE postStatus='Published' AND postDateTime<='2014-11-01 08:42:00' ORDER BY blogEventDate DESC LIMIT 0, 10
Invalid query: SQLSTATE[42S22]: Column not found: 1054 Unknown column 'blogEventDate' in 'order clause'
SELECT FOUND_ROWS() AS `count`
Drew McLellan

Drew McLellan 2638 points
Perch Support

7 years ago

Can you show me your diagnostics report?
Alex Magill

Alex Magill 0 points

7 years ago

Hi Drew, Found the problem - you were right earlier, on my test server I was using an older version of the blog app. Just updated it and it is all looking like it works perfectly.

I'm going to go away and have a quiet word with myself now.

Thanks for your patience.

Alex