Complex Web Application Design with DAO / AR

Hello all -

This is a bit long but I think it can help people who are struggling using yii to build complex applications using DAO & AR.

I started using yii to develop a complex web application a few months ago. My web application is broken up into integrated sub-applications. My plan is to use AR to implement some of the rather simple sub-appls such as the administrative and use DAO to implement the complex sub-apps. Most of my work to date has been on one of the complex sub-apps. This sub-app is composed of several model classes that extend CModel, where each class uses CModel validation and has functions I wrote to perform load and save operations using DAO.

Most of my web pages in the complex sub-app present the user with hierarchical data pulled from the database that could span as many as 5 hierarchy levels and contain a total of 100 or so items in the hierarchy.

For example:

(hierarchy top level to bottom level): category <- group <- item type 1 <- group <- item type 2

The categories and groups levels are solely used to organize the items on the page.

The user has the ability to select / deselect type 1 items and type 2 items and their state is posted back upon clicking save.

My design processes this posted tabular input in the controller. The controller iterates over all of the type 1 posted data, creating an instance of a CModel type 1 class for each. For each type 1 instance, I then iterate over all of its posted type 2 children, creating an instance of a CModel type 2 class for each and adding the type 2 model instance to an array property in its parent type 1 instance.

This allows me to load the posted data in model instances while also capturing their hierarchical relationship.

I validate all of the type 1 and type 2 models using CModel validation and if all are valid, I call the model’s custom save function that iterates over the models, executing a DAO update operation on each.

I have a few questions regarding the design for complex hierarchical tabular input:

  1. Does the DAO appear to be the proper tool for this job as opposed to AR given the hierarchical nature and considering the fact that the data pulled from the database to display the hierarchy spans anywhere from 6 to 10 tables?

(my database is currently normalized - we may denormalize in the future once we are ready to measure performance).

  1. If DAO is the appropriate tool, is it best practice to load the data into models when the data is retrieved from the database for presentation to the user, and when the data is posted by the user? I believe it is the right tool when the data is posted back since the data needs to be validated etc via the model, but when the data is retrieved from the database for display I am not so sure. In that case, the data retrieved from the db would be in an array and the array could simply be passed to the view for display rather than loading it into models first. I know it is a little more expensive to load it into models first, however, you do gain the advantage of dealing with the data in object-oriented manner within the view.

  2. Regardless of whether or not DAO is the right tool, if I were to use AR for such a task, how would I go about doing this?

As I mentioned above, the data pulled together to form the hierarchy on the page spans anywhere from 6-10 tables. I know you can create relations inside an AR model that will allow you to retrieve model instances of related tables but can you capture a 5 level hierarchy where each level is represented by a table and each relationship is one-to-many and be able to easily iterate over the hierarchical data to display, manipulate, etc?

I am having a hard time seeing how such data can be handled using AR…

  1. I have recently given consideration to a hybrid method but am not sure how to connect the dots. In some of the threads I’ve read on this forum regarding DAO and AR, I saw a few suggestions indicating that it may be advantageous to combine the two methods where DAO is used for complex data retrieval and AR is used to perform updates, inserts, and deletes. If I were to use a hybrid method though, I still don’t see how to load all the posted hierarchical tabular data into the AR models. Can you capture all the data in a 5 level hierarchy within the AR models?

Hopefully this discussion can help others who are working on complex applications and are not sure how to use DAO / AR for complex relations and tabular input. Thanks in advance for your insight. It is much appreciated.

i agree with the "4)"

and don’t need to populate a AR . all retrieve query use DAO and all update/delete/insert query use AR .

for select query use associate array to display the data , the CSqlDataProvider and the CArrayDataProvider is suitable .

don’t have to populate a AR after using DAO retrieved the ResultSet .

in one of the applications i work on, i had to go a few levels deep with the table relations using AR.

Think at :

Entity type -> Manufacturer -> Car Model -> Avalable Colors -> Price addition for available colors

And the list can grow, this is just a raw example.

Please note, when using AR, your queries will perform as fast as they perform when using DAO, the difference is that, using AR will eat more memory because it needs to construct the needed objects(RAM is cheap nowadays).

Now, AR provides the relations() method to access related tables. This is fine in most of the cases, but if you need to go many levels deep into relations, then you might suffer performance issues.

What i do, is to create getters for relations and in those getters, to cache the query results, this way improving things.

Now, think this way, in those getters you can also use DAO and instantiate new objects with the results back from DAO, something like:

public function getRelation()


   $results=Yii::app()->getDb()->createCommand('sql')->queryAll();// the sintax might be wrong, i don't use DAO


   foreach($results AS $result){

      $model=new self;




   return $models; 


// and use it like

$model=new Model;


foreach($relation AS $rel){

  // $rel is an instance of CActiveRecord now, so you have all the AR goodies.


Also, i think AR is the way to go into your case, mainly because you’ll keep things nice and clean, i don’t know if it is just me, but using CModel instances for talking with the database is just clumsy.

IMHO, with AR i can work way faster (my time, like yours, costs money) and i have all the AR goodies(events/behaviors/etc) and as the guide says, with proper cache techniques, you can really improve the AR performance almost to the one provided by DAO.

Another thing, you should’t be worried about performance right now, just build your app in the fastest way possible, using AR, then in the future, if the things get slow, apply cache techinques, if this doesn’t suffice either, improve them by translating memory eating relations from AR to DAO.

Hello - thank you for your feedback!

I’m not sure I completely understand how your suggested method using AR is used to capture multiple levels of a hierarchy while also maintaining the relationship between the different levels. In your raw example, you have the following levels:

Entity type -> Manufacturer -> Car Model -> Avalable Colors -> Price

I understand that I could define a relation between Entity Type and each of the other four levels in the Entity Type AR class and populate the relations via a query but that would not capture how, say, Manufacturers retrieved relate to the Car Models retrieved, how the Car Models retrieved relate to the Available Colors retrieved, and how the Available Colors retrieved relate to the Prices retrieved (at least that is my understanding…). It would only give me how the Entity Type relates to each of the other four levels.

Are you suggesting each of the 5 AR classes in the example have a getRelation method to populate the relation of the level below it? In which case, I would retrieve the top level item (Entity Type) call its getRelation method for Manufacturer, iterate over the Manufacturer models retrieved calling its getRelation method for Car Model, etc so I end up with a hierarchy of AR models?

If I am missing your point, could you elaborate? Thanks again.

yes, that’s the point.