ZF-1674: Allow row relationship methods to specify WHERE, ORDER and LIMIT clauses

Description

Rowsets currently allow for the following:-


findDependentRowset($dependentTable, $ruleKey = null)
findParentRow($parentTable, $ruleKey = null)
findManyToManyRowset($matchTable, $intersectionTable, $callerRefRule = null, $matchRefRule = null)

It would be great to extend these method signatures to include custom WHERE, ORDER and LIMIT clauses in much the same way as the parent table fetchAll method so the results can be fine-tuned:-


findDependentRowset($dependentTable, $ruleKey = null, Array $where = array(), $order = null, $limit = null, $offset = null)
findParentRow($parentTable, $ruleKey = null, Array $where = array(), $order = null, $limit = null, $offset = null)
findManyToManyRowset($matchTable, $intersectionTable, $callerRefRule = null, $matchRefRule = null, Array $matchParams = array())

This would then be applied to each applicable method as follows:


public function findDependentRowset($dependentTable, $ruleKey = null, Array $where = array(), $order = null, $limit = null, $offset = null)
{
    ...
    return $dependentTable->fetchAll($where, $order, $limit, $offset);
}

public function findParentRow($parentTable, $ruleKey = null, Array $where = array(), $order = null)
{
    ...
    return $parentTable->fetchRow($where, $order);
}

The many-to-many method is obviously a little more complex as multiple tables are involved, but perhaps an associative array called $matchParams could handle this with a per-table array of where, order, limit and offset:-


$matchParams = array(
    'table1' => array(
        'where' => array('foo in (1,2,3)', 'bar in (3,4,5)'),
        'order' => 'name ASC',
        'limit' => 5
    ),
    'table2' => array(
        'where' => array('foo in (1,2,3)', 'bar in (3,4,5)'),
        'order' => 'name ASC',
        'limit' => 5
    )
);

Comments

I have reported that long time ago. The answer was simple - no.

http://framework.zend.com/issues/browse/ZF-1182

I think we can consider this use case now.

I'd suggest we use the proposal process to design a comprehensive solution to this. I would suggest we use Zend_Db_Select objects as optional arguments to Zend_Db_Table and Zend_Db_Table_Row methods. That way we can specify many SQL clauses without having to add a long list of optional arguments.

I would argue that $order, $count, and $offset are the only additional parameters that would be beneficial to add to the relationship methods.

If you think about it, the final result is to call either fetchAll() or fetchRow(). Since the where clause is being determined within the relationship logic, there's no reason to allow the user to have any say in it. However, if you allow the user to specify $order, $count, and $offset, then it becomes extremely useful. $order would be defined in terms of the related table columns, and not from the table that your current row came from.

I find myself not using the relationship methods and ducking directly into my own queries just to get the ability to order and limit. Seems so unnecessary to me.

The change is very simple and I think it would be hugely beneficial.

You might like to look at the following proposal:

http://framework.zend.com/wiki/display/…

Assigning to [~peptolab] to be resolved with the approved Zend_Db_Table enhancements proposal.

Resolved in r7508