Order Zend_Db_Table rowset by reference column

Question

i know i can define relationships through _referenceMap, i know that i con join selects trough

$db->select()

But what i need is to fetch rowset in model extending Zend_Db_Table_Abstract and then order it by value of referenced column from another table.

Is there some workaround to do that?

edit:

heres is the example:

first table:

table bugs columns id, bugname, authorid

second table:

table authors columns id, authorname

I have a model Model_Bugs extends Zend_Db_Table_Abstract

I want to make something like this:

$model->fetchAll($model->select()->order('authorname ASC'))

This means, that i need to join tables and sort by a column, which is not in the model table.

thanks for help

Jan

Sorry, but it is not clear what you want to do. Can you provide some example of what you want to achieve? — Marcin, Jun 03 '11 at 11:56
In this situation I would create a method (e.g. fetchAllOrderedByAuthor) in the Model_Bugs that would do the join and return the correctly sorted rowset. I think you cannot do it using plain fetchAll method. — Marcin, Jun 03 '11 at 13:26
This is possible, but you cant do joins on zend_db_table, you have to make select with zend_db_select like this: `$select = $db->select() ->from(array('p' => 'products'), array('product_id', 'product_name')) ->join(array('l' => 'line_items'), 'p.product_id = l.product_id', array() ); // empty list of columns` But then you cant use methods defined on a class extending Zend_Db_Table_Row_Abstract, because of the result. — Jaroušek Puchlivec, Jun 03 '11 at 13:30
@Jaroušek Puchlivec. You can do joints with selects using zend_db_table. For example, in your model: `$this->select(Zend_Db_Table::SELECT_WITH_FROM_PART)->setIntegrityCheck(false)->join(....)`. mysql view would also do the job. — Marcin, Jun 03 '11 at 13:36

score 0 · Answer 1 · answered Jul 12 '11 at 23:46

I would add a method in Model_Bugs like so:

public function fetchBugsByAuthorname() {

    $bugTable = $this;
    $bugTableName = $this->info('name');
    $authorsTable = new Model_Authors();
    $authorsTableName = $authorsTable->info('name');

    $select = $bugTable->select()
        ->setIntegrityCheck(false)
        ->from($bugTable, array('id', 'bugname', 'authorid'))
        ->join($authorsTableName, 
            "$bugTableName.authorid = $authorsTableName.id", 
            array("authorname"))
        ->order("$authorsTableName.authorname asc");
    $result = $bugTable->fetchAll($select);

    return $result;
}

But to do this you have to turn off ZF's table integrity checking (setIntegrityCheck(false) above), which means you won't be able to directly call save() on the resulting rows. But if it's for a read-only purpose, it will work.

If you needed to save rowsets back to the database, you may have to first select the author ID's from Model_Authors in the order you want them, and then re-order your Model_Bugs query accordingly. It's messier but it can work.

Order Zend_Db_Table rowset by reference column

1 Answers1