Zend_Db_Table joined queries vs database view

Question

I was wondering what would be the best practices and the best way to achieve consistency while accessing your database data :

Current structure goes as follow

Data Access --> Business Logic --> Controller --> View

My data access layer is composed of a Zend_Db_Table, Zend_Db_TableRowset and a Zend_Db_TableRow for each table.

My business logic is stored in the model named based on a the table

Example of problematic query :

I want to get a specific user based on his username. To do so I have a user table and a role table (role.id is referred as role_id in the user table).

I don't want to have to use the findDependentRowset that will run additionnal queries for each row that get returned. (will get problematic in data grid that display the data as many rows can be returned).

The choices I have (getName() is used in this example to simplify, but it can be any processing) :

Make a custom join on the role table in the user table which will return an index array composed of associative array. In that case I can't call my getName() function defined in my Model_DbTable_User to build the name (first name + middle + last name). Even if I "cast" my array to a Zend_Db_Table_Rowset (or my custom table_rowset) I can't access my custom class method since I get a generic Zend_Db_Table_Row object.
Make a custom join but build the name using CONCAT() in my query at runtime, I still get an array but the name is build so I don't need the getName() method. But if I have specific logic to apply i'm stuck.
Make a view joining user and role tables in my database and create a new set of Zend_DbTable, Zend_DbTableRowset and Zend_DbTableRow. This way I can have specific logic in my database stack.
An ORM (propel or doctrine (1 or 2)), I have no experience with these, I might need more informations to make the right choice.

My alternate goal is to be sure I get consistency in my data structures

ie: array all the way :

array(
    array(row1), 
    array(row2)
);

object all the way

$row = $rowset->current();
$row->field;

Richard Ayotte · Accepted Answer · 2011-12-09 20:19:59Z

1

Creating a view should be done regardless because it complements the other ideas more than it competes with them. The view will:

Abstract the database to something that you can more easily work with.
Be faster because there's no parsing.
Be accessible outside of your application.

Once you've created the views, you can choose a strategy that best solves the problem. If you're creating a form that represents a single entity, then an ORM is probably a good fit. But if you're displaying large lists of data or generating reports that contain many entities then using a declarative language like SQL is probably easier and would perform better.

answered Dec 9, 2011 at 20:19

Richard Ayotte

5,0901 gold badge39 silver badges36 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Zend_Db_Table joined queries vs database view

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related