How to use RDBMS performingly on top of Zend_Db_Table? (if at all ...)

Question

This constant war continues in my mind when I work on the Zend Framework project - a good application design requires that the number of database queries be kept to a minimum. Each query is expensive in terms of both latency and RDBMS computation time. And Zend_Db_Table encourages you to make many requests - there is no built-in way to connect to other Zend_Db_Tables, for example. And there is no way to extract the name of the base table, given the instance of Zend_Db_Table, which makes it difficult to write Zend_Db_Select queries in terms of Zend_Db_Table. In a sense, Zend_Db_Table recommends that you implement the functions that RDBMS already provides in PHP, which is clearly not optimal.

On the other hand, Zend_Db_Table makes tables more like native PHP objects, abstracting the SQL queries themselves. The user does not need to worry about quoting everything frequently, and SQL operations are like simple PHP methods.

Which would be nice, it would be similar to Zend_Db_Table, but for using lazy operations (as Microsoft does with LINQ-to-SQL / Entity Framework / etc.) to compensate for what seems like multiple PHP statements in one (or at least less) RDBMS quer (y | ies).

Is there such a thing? Is it included in Zend or not?

Answer 1

I agree with you that Zend_Db_Table sometimes acts as a hammer in search of a nail. This is not always the most natural tool for writing custom, executable queries with joins. It seems to me that Zend_Db_Table is optimal primarily for a single table without joins. But you can still perform the optimal db query using aspects of the Zend_Db component.

As @singles noted, all this db access should probably be buried inside the model (for an approach like ActiveRecord) or in mapper (for the DataMapper approach) or in an entity manager (e.g. Doctrine2).

For the exmaple of your BlogPost user model, an instance of the custom BlogPost can be added, which will be loaded by the Zend_Db_Adapter instance. All your requests, including your optimized connections, will be in the BlogPost_DataAccess class. SELECT queries in the DataAccess class can be written using soemthing like (pseudocode):

 $select = $adapter->select() ->join() ->join() // etc. ->join() ->where() ->limit() ->order(); 

See what I mean?

Update

What about the member variable $tableMap ? Something like:

 public static $tableMap = array( 'posts' => 'table_posts', 'users' => 'table_users', 'categories' => 'table_categories', // etc ); 

Then in your queries you can use something like:

 $select = $adapter->select(); $select->from(self::$tableMap['users'], array('name', 'email')); 

Maybe add methods called setTableMap($tableMap) , getTableMap() , getTable($k) , setTable($k) , save the original mapping data in the configuration file, and then populate the static member at boot time.

Answer 2

Some points need clarification:

• As you said, Zend_Db_Table does not implement "auto-join", otherwise you could call impatient download . If you need this functionality, check out PHP-AR
• You can implement the loading in Zend_Db_Table yourself - I spent some time thinking about it, and I'm sure it can be done. But I don’t have time for this :)
• But on the other hand, Zend_Db_Table is part of your application model. You need to worry about how your data is extracted, you are interested in the data itself. Going further - if you use the fat model, thin / lean control , you can quickly implement the connection that you need inside the method, which will be enough for most cases.
  Example: http://pastebin.com/pNwideWf (I am linking to the bin insert because code generation does not work properly for me in the editor).

Unfortunately, using this aproach, you cannot save data in joined tables, and you have to keep track of column names: you cannot do $row->joined_table->field_from_joined_table - instead, you can $row->joined_table_field_from_joined_table .

To summarize - if you want to improve performance, use fat models, as mentioned earlier - keep all the data fetching / filtering logic in model classes, in your case Zend_Db_Table .

Answer 3

Take a look at the NotORM database level. This may do what you need;)