[#ZF-1182] Support order, count, offset in findDependentRowset()

Zend Framework

Support order, count, offset in findDependentRowset()

Created: 01/Apr/07 05:12 PM Updated: 27/May/08 08:36 AM

Component/s:

Zend_Db

Affects Version/s:

0.9.1

Fix Version/s:

0.9.3

Time Tracking:

Not Specified

Issue Links:

Duplicate

This issue is duplicated by:

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

Tags:
Participants:	Alan Gabriel Bem, Bill Karwin and Tobias Struckmeier

Description

« Hide

Since Zend_Db_Table_Row_Abstract::findDependentRowset() uses Zend_Db_Table_Abstract::fetchAll() to return value, it could be a good idea to specify also $order, $count, $offset.

Zend_Db_Table_Row_Abstract::findDependentRowset(
    $dependentTable, $ruleKey, 
    $order, $count, $offset);

At this moment Zend_Db_Table_Row_Abstract::findDependentRowset() return all related records, but what if programmer needs only few?

Lets take real life egzample. There's a table of galleries (id, title, description, created_at, updated_at) and related table of images (id, description, created_at, updated_at, gallery_id).

On the main page of this gallery app I want to show galleries and 5 images for each of them. Using Zend_Db_Table_Row_Abstract::findDependentRowset() - without $count, $offset - makes impossible to achieve that. I mean there is no problem, but I do not need all related rows - it could be a reason. of unwanted database overheat (huge amount of related records?).

And what about $order? Assume that each image has its own rate (another column) and images on mentioned main page are highest rated. Once again I am forced to "play" with plain PHP (own select statements/objects).

Cheers, Alan "LBO" Bem.

All

Comments

Work Log

Change History

FishEye

Crucible

Sort Order:

Ascending order - Click to sort in descending order

[ Permlink | « Hide ]

Bill Karwin - 02/Apr/07 04:52 PM

Reformat summary, description.

[ Permlink | « Hide ]

Bill Karwin - 02/Apr/07 05:00 PM

This feature request does not fit easily with the current architecture of table-relationship query methods. Adding these arguments would make the function more complex than it is now.

There is a workaround: use fetchAll() and specify the order, count, offset, or other arguments. But one needs to get an instance of the Table object of the dependent table.

So instead of adding arguments to the findDependentRowset() method, I would rather add a method to the Row class that returns an instance of a dependent table by its ref rule. Then one could run fetchAll() on that table instance.

[ Permlink | « Hide ]

Alan Gabriel Bem - 03/Apr/07 03:49 PM

Hi Bill.
I traced how findDependentRowset() works (in context of improvements I suggested)...

Zend_Db_Table_Row::findDependentRowset() // then
Zend_Db_Table_Abstract::fetchAll() // then
Zend_Db_Table_Abstract::_fetch() // select [order(), limit()] object is used there.

...and it's quite SAFE (and easy) to implement that functionality.

public function findDependentRowset($dependentTable, $ruleKey = null,
+                                   $order = null,
+                                   $count = null,
+                                   $offset = null) 
    { 
        $db = $this->_getTable()->getAdapter();

        if (is_string($dependentTable)) {
            try {
                Zend_Loader::loadClass($dependentTable);
            } catch (Zend_Exception $e) {
                require_once 'Zend/Db/Table/Row/Exception.php';
                throw new Zend_Db_Table_Row_Exception($e->getMessage());
            }
            $dependentTable = new $dependentTable(array('db' => $db));
        }
        if (! $dependentTable instanceof Zend_Db_Table_Abstract) {
            $type = gettype($dependentTable);
            if ($type == 'object') {
                $type = get_class($dependentTable);
            }
            require_once 'Zend/Db/Table/Row/Exception.php';
            throw new Zend_Db_Table_Row_Exception("Dependent table must be a Zend_Db_Table_Abstract, but it is $type");
        }
        $dependentTableClass = get_class($dependentTable);

        $map = $this->_prepareReference($dependentTable, $this->_tableClass, $ruleKey);

        for ($i = 0; $i < count($map[Zend_Db_Table_Abstract::COLUMNS]); ++$i) {
            $cond = $db->quoteIdentifier($map[Zend_Db_Table_Abstract::COLUMNS][$i]) . ' = ?';
            $where[$cond] = $this->_data[$map[Zend_Db_Table_Abstract::REF_COLUMNS][$i]];
        }
        return $dependentTable->fetchAll($where,
+                                        $order = null,
+                                        $count = null,
+                                        $offset = null) ;
    }

I think there is no need to sanitize $order, $count or $offset as it's carried out later in Zend_Db_Select object.

P.S. findParentRow() could be improve as well - it is almost identicall [maybe new issue (DRY filosophy)] to findDependentRowset().

[ Show » ]

Alan Gabriel Bem - 03/Apr/07 03:49 PM Hi Bill. I traced how findDependentRowset() works (in context of improvements I suggested)...

Zend_Db_Table_Row::findDependentRowset() // then
Zend_Db_Table_Abstract::fetchAll() // then
Zend_Db_Table_Abstract::_fetch() // select [order(), limit()] object is used there.

...and it's quite SAFE (and easy) to implement that functionality.

public function findDependentRowset($dependentTable, $ruleKey = null,
+                                   $order = null,
+                                   $count = null,
+                                   $offset = null) 
    { 
        $db = $this->_getTable()->getAdapter();

        if (is_string($dependentTable)) {
            try {
                Zend_Loader::loadClass($dependentTable);
            } catch (Zend_Exception $e) {
                require_once 'Zend/Db/Table/Row/Exception.php';
                throw new Zend_Db_Table_Row_Exception($e->getMessage());
            }
            $dependentTable = new $dependentTable(array('db' => $db));
        }
        if (! $dependentTable instanceof Zend_Db_Table_Abstract) {
            $type = gettype($dependentTable);
            if ($type == 'object') {
                $type = get_class($dependentTable);
            }
            require_once 'Zend/Db/Table/Row/Exception.php';
            throw new Zend_Db_Table_Row_Exception("Dependent table must be a Zend_Db_Table_Abstract, but it is $type");
        }
        $dependentTableClass = get_class($dependentTable);

        $map = $this->_prepareReference($dependentTable, $this->_tableClass, $ruleKey);

        for ($i = 0; $i < count($map[Zend_Db_Table_Abstract::COLUMNS]); ++$i) {
            $cond = $db->quoteIdentifier($map[Zend_Db_Table_Abstract::COLUMNS][$i]) . ' = ?';
            $where[$cond] = $this->_data[$map[Zend_Db_Table_Abstract::REF_COLUMNS][$i]];
        }
        return $dependentTable->fetchAll($where,
+                                        $order = null,
+                                        $count = null,
+                                        $offset = null) ;
    }

I think there is no need to sanitize $order, $count or $offset as it's carried out later in Zend_Db_Select object. P.S. findParentRow() could be improve as well - it is almost identicall [maybe new issue (DRY filosophy)] to findDependentRowset().

[ Permlink | « Hide ]

Alan Gabriel Bem - 03/Apr/07 03:55 PM

Please, forget my Post Scriptum. I was hurry and not even think about this.

Cheers.

[ Permlink | « Hide ]

Bill Karwin - 17/Apr/07 06:21 PM

This is not in the scope of the Table Data Gateway implementation of Zend_Db.

The recommended solution is to use the Zend_Db_Select class:

$table = new MyTable();

$select = $table->getAdapter()->select();

With the $select object, you can issue a query using joins, order, count, offset, where clause, etc.

[ Permlink | « Hide ]

Bill Karwin - 17/Apr/07 06:22 PM

Reopening so we can resolve it as Won't Fix.

[ Permlink | « Hide ]

Bill Karwin - 17/Apr/07 06:22 PM

Resolving as Won't Fix.

[ Permlink | « Hide ]

Tobias Struckmeier - 27/May/08 07:59 AM

I am missing here a bit an explanation why it is not in the scope? It would be usefull and not harming anything existing.

[ Permlink | « Hide ]

Bill Karwin - 27/May/08 08:36 AM

A solution has already been implemented to allow arbitrary SELECT objects to be passed to find() and fetch() methods of Zend_Db_Table and Zend_Db_Table_Row.

See http://framework.zend.com/wiki/display/ZFPROP/Zend_Db_Table+Query+Enhancements+-+Simon+Mundy for details.