How to split two tables in zend framework

Question

Books have the following fields

• book_id
• book_name
• book_auther
• book_pub_date

Category table

• category_id
• category_name

allocation table has

• PLACEMENT_ID
• placement_category_id (FC)
• placement_book_id (FC)

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

---

Note. I have divided the model for each table, and all the tables are linked together using $ _referenceMap
I am using $ adapter = new Zend_Paginator_Adapter_DbTableSelect ($ select);
question: how to make $ select?

Answer 1

You have a many-to-many relationship between books and categories, and the placement table is the intersection table. Thus, I think that one way your $ select can be created is to use an internal 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()); 

Directly, the names of the tables and models should match your real names. Another way is to create a view in your database. Then you create a model for the view. That would make $ select shorter.

Answer 2

You can use this pagination class:

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

it's very simple to configure and use, and then apply it to a JOINed query that selects all books from a specific category (which many here will explain better than me).