Pass $data in fetchAll() to the TableGatewayPaginator

[michalbundyra]

Per the mailing list, it may make sense to pass the $data argument of DbConnectedResource::fetchAll() to the TableGatewayPaginator (as the second argument, forming the $where clause).

However...

It may also make sense to map query string parameters to the clause they should be used in. Zend\Paginator\Adapter\DbTableGateway has the following arguments:

• $tableGateway - already being passed, for obvious reasons.
• $where - to create the WHERE clause
• $order - to sort the returned values
• $group - for GROUP BY clauses
• $having - for HAVING clauses

Each of the latter four can take an associative array of arguments.

As such, this feature should likely not pass $data directly, but instead map parameters to arguments... which means a related UI feature.

---

Originally posted by @weierophinney at https://github.com/zfcampus/zf-apigility/issues/29

[michalbundyra]

I think having this feature built-in would be very helpful, even without UI if it was well documented. Most use cases could probably be covered with some sensible default implementation. I posted something I put together quickly in the mailing list discussion.

---

Originally posted by @cvuorinen at https://github.com/zfcampus/zf-apigility/issues/29#issuecomment-39494211

[michalbundyra]

Can't wait to see this happen! :+1:

---

Originally posted by @mrova at https://github.com/zfcampus/zf-apigility/issues/29#issuecomment-69602838

[michalbundyra]

It would be very useful for RAD :+1:

---

Originally posted by @jahd2602 at https://github.com/zfcampus/zf-apigility/issues/29#issuecomment-112923455

[michalbundyra]

We made a simple implementation of this, which only enables WHERE and ORDER. e.g. GET resource?name=hello&foo=bar&sort=world&order=ASC

use Zend\Db\TableGateway\TableGatewayInterface as TableGateway;
use Zend\Paginator\Adapter\DbTableGateway as TableGatewayPaginator;
use ZF\Apigility\DbConnectedResource as ParentDbConnectedResource;

class DbConnectedResource extends ParentDbConnectedResource
{
    public function fetchAll($data = array())
    {
        $where = $data->getArrayCopy();
        if (isset($where['sort'])) {
            $order = strtolower($where['sort']);
            unset($where['sort']);
        }

        if (isset($where['order']) && strlen($order) > 0) {
            $orderDirection = strtoupper($where['order']);
            unset($where['order']);

            $order .= in_array($orderDirection, array('ASC', 'DESC')) ? ' ' . $orderDirection : '';
        }

        // We won't implement GROUP BY or HAVING conditions yet, they are
        // out of our RESTful API scope at the moment.
        $adapter = new TableGatewayPaginator($this->table, $where, $order);
        return new $this->collectionClass($adapter);
    }
}

Not sure if GROUP BY even belongs to the RESTful API domain. Sure it wouldn't hurt and might have some use cases. We didn't implement it.

It seems that in proper RESTful API implementation you use GET resource?sort=+field_name and GET resource?sort=-field_name for sorting; we didn't implement this, because the plus sign has to be urlencoded and we want to see if this would add conditional implementations in other parts of our system; we probably will refactor to this however, since multi-sorts would be much prettier: GET resource?sort=+field_A,-field_B

You need to add this class to the namespace of your project and define Add the line 'resource_class' => 'YourNameSpace\DbConnectedResource', under configuration key zf-apigility/db-connected/.

Then you need to add 'sort' and 'order' to your Collection Query Whitelist among with all the field names you want to be able to filter by.

---

Originally posted by @AhtiAhde at https://github.com/zfcampus/zf-apigility/issues/29#issuecomment-127560005

[michalbundyra]

When Apigility realeases a proper implementation, it would be quite important that the solution complies with the best practices of RESTful API world: GET resource?name=hello&foo=bar,baz&sort=+world,-foo, would return all resources with name hello, and field foo having values bar or baz, sorted by ascending world field and then listing the resources with baz before bar (descending order).

http://www.vinaysahni.com/best-practices-for-a-pragmatic-restful-api#advanced-queries

http://blog.mwaysolutions.com/2014/06/05/10-best-practices-for-better-restful-api/#highlighter_206760

---

Originally posted by @AhtiAhde at https://github.com/zfcampus/zf-apigility/issues/29#issuecomment-127620833