Pimcore: List objects with DateTime object matching a provided year and month

Question

My agency is building filtering controls for a blog deployment on Pimcore and we are trying to filter the posts by a given YYYY-MM combination. We have filtering working for author objects and subject objects.

Here is the code block in our controller applying the filters and returning posts...

// Pagination page...
$page = (int)$this->getParam("page") < 1 ? 1 : (int)$this->getParam("page");

// Results per page...
$rpp = (int)$this->document->getProperty( 'resultsPerPage' ) < 1 ? 1 : (int)$this->document->getProperty( 'resultsPerPage' );

// Return list of posts...
$list = new Object\BlogArticle\Listing();

// Apply author filter...
if( $this->getParam( 'filter-by' ) == 'author' ) {
    $list->setCondition( 'author LIKE ?', "%,".(int)$this->getParam( 'author' ).",%" );
}

// Apply category filter...
if( $this->getParam( 'filter-by' ) == 'category' ) {
    $list->setCondition( 'categories LIKE ?', "%,".(int)$this->getParam( 'category' ).",%" );
}

// This isn't working!
if( $this->getParam( 'filter-by' ) == 'archive' ) {
    $list->setCondition( "DATE_FORMAT(FROM_UNIXTIME(date), '%Y-%m') = ".$list->quote( $this->getParam( 'archive' ) ) );
}

// Order by date...
$list->setOrderKey( "date" );
$list->setOrder( $this->document->getProperty( 'resultsSort' ) == 'asc' ? 'asc' : 'desc' );

// Apply pagination values...
$list->setLimit( $rpp );
$list->setOffset( ( $page - 1 ) * $rpp );

// Do it!
$this->view->blog = $list->getObjects();

The $this->getParam( 'archive' ) equals something along the lines of 2016-06. The Zend DateTime object is bound to the date column.

I've looked through the Pimcore documentation and I cannot find anything on how to query an object's DateTime field.

Answer 1

Your approach seems perfectly valid and it should work. There is just one mistake in the date function name. It should be FROM_UNIXTIME and not FROMUNIXTIME.