Auto Sorting Zend_Db_Table_Rowsets with Pagination

Expanding upon my abstract DB class that auto creates Zend_Paginators on demand, I thought it was about time I did something regarding automatically sorting the records returned.

First thing I did was go in and edit my db base class (need a reminder? https://delboy1978uk.wordpress.com/2014/06/30/auto-paginator-with-zend_db_table/), adding a sort_field property, a setSortField() method, and a little if statement in our getResult() method :

    /** @var string $sort_field */
    protected $sort_field = null;
    /**
     *  For instance 'date_added DESC'
     *  @param int $no
     *  @return $this
     */
    public function setSortField($sort_and_direction)
    {
        $this->sort_field = $sort_and_direction;
        return $this;
    }
    /**
     * @param Zend_Db_Select|string $select
     * @return \Zend_Db_Table_Rowset_Abstract|Paginator
     */
    public function getResult($select)
    {
        if(is_string($select))
        {
            $select = $this->select()->where($select);
        }
        if($this->sort_field)
        {
            $select->order($this->sort_field);
        }
        if($this->paginator_flag == true)
        {
            return $this->getPaginator($select);
        }
        return $this->fetchAll($select);
    }

Now we have that, we can sort any queries like so:

        // Get the page number
        $page = ($this->getRequest()->getParam('page')) ?: 1;
                
        // Set pagination
        $db->setPaginatorFlag(true);
        $db->setNumPerPage(30);
        $db->setPage($page);
        
        // Get sort field
        $sort = $this->getRequest()->getParam('sort');
        $dir = $this->getRequest()->getParam('dir');
        
        // Set the sort
        $db->setSortField($sort.' '.$dir);

Again, in your DB class method, make sure it calls $this->getResult(); and not fetchAll().

Anyway, thats only one half of the equation, what we need is sort links! I create a view helper for this, which basically calls the url view helper, but with additional search capabilities. In the Bootstrap (as you’ll know), we define our routes as such:

$router->addRoute(
    'lead-assessor-view-supply',
    new Zend_Controller_Router_Route(
        '/lead-assessor/view-supply/:orgid/:id/:page/:sort/:dir',
         array(
            'controller' => 'lead-assessor',
            'action' => 'view-supply', 
            'page' => 1, 
            'sort' => 'date_from', 
            'dir' => 'DESC'
         )
      )
);

As you can see, I have added sort and dir in addition to page.  To get the links working, send the page, sort, and dir variables to the view, and in the view, instead of calling $this->url(blahblah); we will call our own view helper, sortLinks(); Here’s the class:

<?php 

class ESOS_View_Helper_SortLinks extends Zend_View_Helper_Abstract
{
    /** 
     *  Pass in a route name, and an array of any params which are the same for all the sort links
     *  @param string $route_name
     *  @param array $params
     *  @param string $field_name
     *  @param string $sort_field
     *  @param string $sort_dir
     *  @param string $field_label
     *  @param string $classes
     *  @return string
     */
    public function sortLinks(array $params,$route_name,$field_name,$field_label, $classes = null)
    {
        // Set the current sort field
        $current_sort_field = isset($params['sort']) ? $params['sort'] : 'ASC' ;
        
        // Set the Icon
        $icon = $this->view->sortIcon($field_name,$current_sort_field,$params['dir']);
        
        // Set params
        $params['sort'] = $field_name;
        $params['dir'] = $this->view->sortDir($field_name, $current_sort_field, $params['dir']);
        
        // Build the HTML
        $link = '<a href="';
        $link .= $this->view->url($params,$route_name);
        $link .= '" class="';
        $link .= $classes;
        $link .= '">';
        $link .= $icon;
        $link .= $field_label;
        $link .= '</a>';
        
        // Return the HTML
        return $link;
    }
}

Almost there! You’ll see it is calling another two view helpers, sortIcon and sortDir:

<?php

class ESOS_View_Helper_SortDir extends Zend_View_Helper_Abstract
{
    public function sortDir($field_name, $current_sort_field, $dir)
    {
        if($field_name == $current_sort_field)
        {
            switch($dir)
            {
                case 'ASC':
                    $dir = 'DESC';
                    break;
                case 'DESC':
                default:
                    $dir = 'ASC';
                    break;
            }
        }
        return $dir;
    }
}
<?php

class ESOS_View_Helper_SortIcon extends Zend_View_Helper_Abstract
{
    public function sortIcon($field_name, $current_sort_field, $dir)
    {
        if($field_name == $current_sort_field)
        {
            switch($dir)
            {
                case 'ASC':
                    $icon = '<i class="fa fa-caret-up"></i>&nbsp;';
                    break;
                case 'DESC':
                default:
                    $icon = '<i class="fa fa-caret-up"></i>&nbsp;';
                    break;
            }
        }
        else 
        {
            $icon = null;
        }
        return $icon;
    }
}

These just figure out which direction of sort the link will generate. The Icons are from Font Awesome, but feel free to use anything you like!

Tying it all together, in your view, define an array of any parameters that you will pass into each sort link (sort, and dir should always be in there, page if using the paginator):

    // Common params for the sortLinks
    $params = array(
        'orgid' => $this->org->getId(),
        'page' => $this->page,
        'sort' => $this->sort,
        'dir' => $this->dir,
    );

At the top of your table where you want your clickable sort links, call:

<?= $this->sortLinks($params,'portfolio-admin-users','name',$this->t('Organisation')); ?>

Sorting By Price

Sorting By Status

This will save a LOT of farting around in the future! Have fun! 🙂

Overriding DB Connected Resources in Apigility

Apigility rocks! It’s still in its early beta days (and hence a little buggy at times), but it’s WELL worth playing with now for building your API’s!

Anyway, you can create code connected and db connected services. Db connected REST services pretty much give you the auto CRUD functionality, but it would be nice to have some where, and order clauses!

This feature should be ready for version 1.2, but in the meantime, lets override it mannually!

You will notice in your Rest/ServiceName folder (in my particular case, Agents), that it has auto generated an AgentsCollection and an AgentsEntity PHP file.

We will create our own Resource Class which will extend the DBConnectedResource. So I created AgentsResource.php :

 

 <?php

namespace Allagents\V1\Rest\Agents;
use ZF\Apigility\DbConnectedResource;
use Zend\Paginator\Adapter\DbTableGateway as TableGatewayPaginator;

class AgentsResource extends DbConnectedResource
{
 public function fetchAll($data = array())
 {
 // WHERE CLAUSE
 $where = '';
 foreach($data->where as $clause)
 {
 $where .= $clause.' AND ';
 }
 //chop off the last AND
 $where = substr($where,0,-4);

 // ORDER CLAUSE
 $order = '';
 foreach($data->order as $clause)
 {
 $order .= $clause.' , ';
 }
 //chop off the last comma
 $order = substr($order,0,-2);

 // GROUP CLAUSE
 $group = '';
 foreach($data->group as $clause)
 {
 $group .= $clause.' , ';
 }
 //chop off the last comma
 $group = substr($group,0,-2);

 // HAVING CLAUSE
 $having = '';
 foreach($data->having as $clause)
 {
 $having .= $clause.' AND ';
 }
 //chop off the last AND
 $having = substr($having,0,-4);
 $adapter = new TableGatewayPaginator($this->table,$where, $order, $group, $having);
 return new $this->collectionClass($adapter);
 }
}

Ok, now we have to tell ZF2 to use our resource class instead of the default. As always, ZF2 modules are mega customisable, and usually involves tweaking your module.config. Apigility is no different, so jump into your module folder and open the module.config.php. We are adding one line to our DBConnectedResource. Find the section ‘zf-apigility’ => ‘db-connected’ and you will see your service’s entry. We add an array key like so; ‘resource_class’ => ‘Allagents\\V1\\Rest\\Agents\\AgentsResource’,

'zf-apigility' => array(
 'db-connected' => array(
 'Allagents\\V1\\Rest\\Agents\\AgentsResource' => array(
 'adapter_name' => 'DBAdapter',
 'table_name' => 'agents',
 'hydrator_name' => 'Zend\\Stdlib\\Hydrator\\ArraySerializable',
 'controller_service_name' => 'Allagents\\V1\\Rest\\Agents\\Controller',
 'entity_identifier_name' => 'aid',
 'resource_class' => 'Allagents\\V1\\Rest\\Agents\\AgentsResource',
 ),
 ),
 ),

Lastly, we need to whitelist the query parameters that we use, otherwise they will not appear in the HAL links!

Again, in the module.config.php, in the section ‘zf-rest’, we add an array defining the whitelisted parameters:

'zf-rest' => array(
 'Allagents\\V1\\Rest\\Agents\\Controller' => array(
 'listener' => 'Allagents\\V1\\Rest\\Agents\\AgentsResource',
 'route_name' => 'allagents.rest.agents',
 'route_identifier_name' => 'agents_id',
 'collection_name' => 'agents',
 'entity_http_methods' => array(
 0 => 'GET',
 1 => 'PATCH',
 2 => 'PUT',
 3 => 'DELETE',
 ),
 'collection_http_methods' => array(
 0 => 'GET',
 1 => 'POST',
 ),
 'collection_query_whitelist' => array(
 0 => 'where',
 1 => 'order',
 2 => 'having',
 3 => 'group',
 ),
),


To test it is working, call your URL (in my case /agents) and you should see the normal expected results. Now test with a query sting. I made a little script to build the query up:

<?php

$query = http_build_query(array(
 'where' => array(
 'name LIKE "A%"',
 ),
 'order' => array(
 'name asc',
 ),
 'group' => array(

 ),
 'having' => array(

 ),
));

echo $query.'<br />';

Which output:
where%5B0%5D=name+LIKE+%22A%25%22&order%5B0%5D=name+asc
So I called: /agents?where%5B0%5D=name+LIKE+%22A%25%22&order%5B0%5D=name+asc
You should have your filtered results, with HAL links in the JSON, like so!

{
“_links”: {
“self”: {
“href”: “http://api.allagents.del/agents?where%5B0%5D=name%20LIKE%20%22A%25%22&order%5B0%5D=name%20asc&page=1&#8221;
},
“first”: {
“href”: “http://api.allagents.del/agents?where%5B0%5D=name%20LIKE%20%22A%25%22&order%5B0%5D=name%20asc&#8221;
},
“last”: {
“href”: “http://api.allagents.del/agents?where%5B0%5D=name%20LIKE%20%22A%25%22&order%5B0%5D=name%20asc&page=44&#8221;
},
“next”: {
“href”: “http://api.allagents.del/agents?where%5B0%5D=name%20LIKE%20%22A%25%22&order%5B0%5D=name%20asc&page=2&#8221;
}
},
“_embedded”: {
“agents”: [
{
“aid”: “2842”,
“etc”:”etc!”

Have fun! Maybe I could even get this into the actual Apigility code by sending them a Pull Request! (Probably not though lol, I’m sure they’ll figure a better way of dealing with the query params than passing RAW SQL portions haha!)