How to paginate two tables in zend framework

Question

Books has the following fields

• book_id
• book_name
• book_auther
• book_pub_date

category table has

• category_id
• category_name

placement table has

• placement_id
• placement_category_id(FK)
• placement_book_id(FK)

Now we want to use pagination in index controller to select books with specific category ?
all tables are in one database

---

Note: I have separated model for each table and all tables are related each other with $_referenceMap
I use $adapter = new Zend_Paginator_Adapter_DbTableSelect($select);
the question is : how to make $select ?

Answer 1

You have many-to-many relationship between books and categories, and your placement table is an intersection table. Thus I think that one way your $select could be constructed is using inner join as follows:

    $placementModel = new Your_Model_Table_Placement();

    $select = $placementModel->select(Zend_Db_Table::SELECT_WITH_FROM_PART)->setIntegrityCheck(false);
    $select->joinInner('BOOKS', 'BOOKS.book_id = PLACEMENT.placement_book_id');
    $select->where('PLACEMENT.placement_category_id = ?', $categoryID);

    $adapter = new Zend_Paginator_Adapter_DbTableSelect($select);

    // check the result if they are what you expect
    var_dump($adapter->getItems(0, 5)->toArray());

Off course the names of tables and models must match your real names. Another way would be to create a view in your database. Then you would create a model for the view. This would make the $select shorter.

Answer 2

You can use this pagination class:

http://www.catchmyfame.com/2007/07/28/finally-the-simple-pagination-class/

it's really simple to setup and use, and then you apply it to a JOINed query which selects all the books from the specific category (which many here would explain better than me).