Zend_Db_Table + many-to-many + Zend_Paginator

[risoknop]

Well, let's take a look at following model method:

    public function getPages($category)
    {
        $select = $this->select();
        $select->where('id = ?', $id);
        $category = $this->fetchRow($select);
        return $category->findManyToManyRowset('Pages', 'JoinPagesCategories');
    } 

Its purpose is really straightforward - it looks up all pages related to a category.

The problem:

I need to paginate the results from the method above and I would like to pass a Zend_Db_Select to the paginator instead of already fetched results for performance reasons (if there are too many pages under a category, why not paginate it).

But the following doesn't work:

    public function getPages($category)
    {
        $select = $this->select();
        $select->where('id = ?', $id);
        return $select->findManyToManyRowset('Pages', 'JoinPagesCategories');
    } 

Any ideas? Just asking.

I know Zend_Db_Select doesn't have findManyToManyRowset() method so the above was just a desperate attempt. I have read through the whole Zend_Db_Table documentation and this isn't adressed there so I'm wondering if it's possible (imho it should be... this isn't an uncommon situation).

[imaginethis]

What's wrong with just using joins now?

[risoknop]

What's wrong with just using joins now?

Well, personally I find it easier to use findManyToManyRowset() or findDependantRowset(). It's more readable than 5-7 lines of select joins.

I just don't understand why you cannot just apply it to a select object and than pass it to Zend_Paginator. That makes it almost unusable when dealing with larger databases.

[imaginethis]

Well if you want it that badly you can always extend Zend_Db_Select to suit your needs. But I assure you it's far less time consuming to just writ it manually. Also if your using Zend_Db_Table you can take advantage of magic functions.

$bugsTable = new Bugs();
$bugsRowset = $bugsTable->find(1234);
$bug1234 = $bugsRowset->current();
$products = $bug1234->findProductsViaBugsProducts();
$products = $bug1234->findProductsViaBugsProductsByBug(); 

But honestly it's far more readable to just use Zend_Db_Select::joineLeft.

[risoknop]

Well if you want it that badly you can always extend Zend_Db_Select to suit your needs. But I assure you it's far less time consuming to just writ it manually. Also if your using Zend_Db_Table you can take advantage of magic functions.

$bugsTable = new Bugs();
$bugsRowset = $bugsTable->find(1234);
$bug1234 = $bugsRowset->current();
$products = $bug1234->findProductsViaBugsProducts();
$products = $bug1234->findProductsViaBugsProductsByBug(); 

But honestly it's far more readable to just use Zend_Db_Select::joineLeft.

I know about the magic functions. My point was that both magic functions and findManyToManyRowset() fetch all rows from the database. And that basically contradicts the point of pagination.

The point of pagination is to only fetch those rows that are needed for current page, not whole rowset. That's why you can pass Zend_Db_Select object to Zend_Paginator.

But with relational databases this is impossible, you are forced to use innerJoin() or leftJoin(). They should make it that you could apply findManyToManyRowset() to a select object... that would solve the problem.