Zend_Db - model objects, relationships, and complex conditions

1. Data model, tables and relationships

Many wondered how to set the method correctly.

$ articles-> findAllByCategoryId ($ categoryId);
or
$ category-> findAllArticles ();

Moreover, both options imply a binding of objects - $ category “ knows ” about $ articles or vice versa.

Is it possible to do without such methods that know too much? Someone prefers doctrine, but I went my own way - I wrote several classes, inheriting them from Zend_Db_Table_Abstract and Zend_Db_Table_Row_Abstract
')
Goals were
1) get a thin and easy to read controller ,
(Surprisingly, the model with this approach also remained not very thick)
2) make humanized model objects,
3) get rid of the heap implementation of getAllSomethingByAnythingAlsoWhere (...) methods
4) and also to ensure that the models do not know anything about each other (*).

I tried to do without using in the controller and the model of such words as select, adapter, fetch, Zend_Db_Expr, quote, ... well, this is personal.

It is easier to look at everything with an example, which, although it is a “spherical horse in a vacuum,” is rather illustrative.

Controller:

<?php
class User_IndexController extends EXT_Controller_Action
{
/* Action 1 */
public function indexAction()
{
$table = new User_Model_DbTable_Users();
$users = $table->with( 'Address' , 'User_Model_DbTable_Address' , 'address_id' )
->with( 'Location' , 'User_Model_DbTable_Locations' , 'Location.id = Address.location_id' )
->addRelationMany( 'Places' , 'User_Model_DbTable_Address' , 'user_id' )
->addRelationMany( 'Friends' , 'User_Model_DbTable_Friends' , 'user_id' , true )
->findAll();
$ this ->view->users = $users;
}

/* Action 2 */
public function listAction()
{
$table = new User_Model_DbTable_Users();
$users = $table->withRelated()
->addRelationMany( 'Places' , 'User_Model_DbTable_Address' , 'user_id' )
->addRelationMany( 'Friends' , 'User_Model_DbTable_Friends' , 'user_id' , true )
->addFilter(array( 'OR' ,
array( 'name' , 'LIKE' , 'Alex%' ),
array( 'AND' ,
array( 'Location.title' , '=' , 'Swietokrzyskie' ),
array( 'Address.street' , 'LIKE' , 'Sovi%' ) )))
->setOrder( 'created_at DESC' )
->setLimit(10)
->findAll();
$ this ->view->users = $users;
}
}
* This source code was highlighted with Source Code Highlighter .

But the database objects (model):

/* */
class User_Model_DbTable_Users extends EXT_Db_Table
{
/* */
protected $_name = 'user__users' ;
/* - */
protected $_rowClass = 'User_Model_User' ;
/* . , $_relations */
protected $_relations = array( 'Address' => array( 'User_Model_DbTable_Address' , 'address_id' ),
'Location' => array( 'User_Model_DbTable_Locations' , 'Location.id = Address.location_id' ));
/* id () */
protected $_isIndexedRowset = true ;

const AUTH_NAMESPACE = 'auth' ;

/* . */
public function loadDefaultFilters()
{
if (!$ this ->isLoadDefaultFilters()) {
return $ this ;
}
$ this ->addFilter( 'is_deleted' , '<>' , 1);
return $ this ;
}
}

/* */
class User_Model_User extends EXT_Db_Table_Row
{
}

/* */
class User_Model_DbTable_Address extends EXT_Db_Table
{
protected $_name = 'user__address' ;
protected $_rowClass = 'User_Model_Address' ;
protected $_relations = array( 'Location' => array( 'User_Model_DbTable_Locations' , 'location_id' ),
'User' => array( 'User_Model_DbTable_Users' , 'user_id' ),);
}

/* */
class User_Model_Address extends EXT_Db_Table_Row
{
}

/* , , user'' */
class User_Model_DbTable_Friends extends EXT_Db_Table
{
protected $_name = 'user__friends' ;
protected $_rowClass = 'User_Model_Friend' ;

protected $_relations = array( 'User' => array( 'User_Model_DbTable_Users' , 'user_id' ),
'Friend' => array( 'User_Model_DbTable_Users' , 'friend_id' ));
}
* This source code was highlighted with Source Code Highlighter .

In the example, two almost identical action-a. Selection of users.

The main SQL query gets data that is tied to the one-to-one user table using -> with () and will be accessible via user objects without additional queries to the database like this:
$ user-> getLocation () -> title // synonym $ user-> Location__title
$ user-> getAddress () -> street // synonym $ user-> Address__street
Naturally, there are no getAddress () and getLocation () functions — they are implemented dynamically via __call ().

Here I will return to the top of the topic.
My view template uses
$ user-> getAllFriends () // get all the friends of the user
$ user-> getAllPlaces () // get all user addresses
These non-existent (also __call () - call) methods refer to the connections specified in the controller :
1) $ table-> with (...) // sets the “to-one” relationship - this data will be included in the main query findAll (), findRow (), findAllBy (), findRowBy ().
You can specify only the required attached fields in the query, reducing it.
Even in with you can not pass the name of the class, but for example $ friendsTable - a table with established filters and its own connections.
Generally describing all the nuances can go longer than writing the classes themselves. :)
2) $ table-> addRelationMany (...) // sets the connection “to many” - the data is not included in the request, but will be loaded from the database at the first direct access ($ user-> getAllFriends ())
Binding field can be set by name, name + prefix, full normal condition of field equality.

Those. the models themselves are not related, but the connections can be used transparently.

* You probably noticed protected $ _relations = ...
Still, there are obvious, constantly used links that, for convenience, you can (if you really want) write in a single (!) Place in the class header, without confusing objects with redundant links.
In my second action, the $ table-> withRelated () method says that these links should be included in the main query.
If this is not done, then when $ user-> getAddress () is called, SQL will be additionally called for each user.
(Note: 'Location' is a table of the country-country-city tree)

2. Filters - sample conditions

I believe that Zend_Db does not have a good where () builder - conditions for SQL queries.
Those. the condition (field_a = 1 OR field_b = 2) AND (field_c = 1 OR field_d = 2) already causes some difficulties in construction.
In the second action-e of my controller, I gave an example of building complex conditions based on my self-made class of filters.
This filter normally "digests" the equality / inequality signs in IS NULL / IS NOT NULL, IN (1,2,3) / NOT IN (1,2,3), etc.
Here is the monster construct that was built by this filter in conjunction with findAll () in the end:

SELECT `user__users`.*,
`Address`.`id` AS `Address__id`,
`Address`.`street` AS `Address__street`,
`Address`.`house` AS `Address__house`,
`Address`.`floor` AS `Address__floor`,
`Address`.`flat` AS `Address__flat`,
`Address`.`location_id` AS `Address__location_id`,
`Address`.`user_id` AS `Address__user_id`,
`Location`.`id` AS `Location__id`,
`Location`.`parent_id` AS `Location__parent_id`,
`Location`.`root_path` AS `Location__root_path`,
`Location`.`title` AS `Location__title`,
`Location`.` level ` AS `Location__level`,
`Location`.`code` AS `Location__code`
FROM `user__users`
LEFT JOIN `user__address` AS `Address`
ON user__users.address_id=Address.id
LEFT JOIN `user__locations` AS `Location`
ON Location.id = Address.location_id
WHERE (((`user__users`.`is_deleted` != 1)
AND ((`user__users`.`name` LIKE 'Alex%' )
OR ((`Location`.`title` = 'Swietokrzyskie' ) AND (`Address`.`street` LIKE 'Sovi%' )))))
ORDER BY `created_at` DESC
LIMIT 10
* This source code was highlighted with Source Code Highlighter .

There is a nuance with an extra pair of brackets, but this does not affect the correctness of the request.
In general, simpler sampling conditions are used more often than in the example:
-> addFilter ('created_at', '> =', $ dateStart)
-> addFilter ('is_new', '=', 1)
Filters specified in loadDefaultFilters () are applied automatically, unless clearFilters (false) or setIsLoadDefaultFilters (false) are cleared by this. The sorting and default limit are also set.

I put my add-ons on Zend in a separate library. Requires refactoring and other grinding, but nevertheless it is already operational and quite comfortable (imho).
Download - there is only a library, a module and tables from the example.
You can “pick it up” or screw it to your Zend application, run it and pick it up again.

PS If it will be interesting, I can lay out another description of my ACL based on Zend_Acl - there are interesting, perhaps DISPUTABLE (as well as the above) solutions. And also a class of a simple tree Tree without nested sets - there were reasons to do it without. In addition, nested sets for Zend have already been successfully implemented without me.

Pps
I forgot to show the view template. And I wonder why so much attention is paid to the controller.
Here is actually:

< div class ="container2" >
< div class ="container" >
<? php foreach ($ this- > users as $user):? >
< div >
<? = $user- > name ? > ( <? = $user- > email ? > ),
<? = $user- > Location__title ? > <? = $user- > getAddress()- > street ? > , <? = $user- > Address__house ? >
< ul >
<? php foreach ($ user- > getAllPlaces() as $place):? >
< li ><? = $place- > street ? > <? = $place- > house ? > <? = $place- > getLocation()- > title ? ></ li >
<? php endforeach ;? >
</ ul >
< ul >
<? php foreach ($ user- > getAllFriends() as $friend):? >
< li > friend: <? = $friend- > Friend__name ? > </ li >
<? php endforeach ;? >
</ ul >
</ div >
<? php endforeach ;? >
</ div >
</ div >

* This source code was highlighted with Source Code Highlighter .