Active Record And Query Builder

Yii 1.1 General Discussion
1

Hi

Two way to work with DB. Active Record (with CDB Criteria) way and Query builder,As reading posts/article I found that preferences always give to Active record . What is my point is for single table Active record is great But for joins It look like Query builder is more easy/optimized. For Active record first we need to set relations then after using with clause we get the all columns from all joined tables. Is I am missing some thing here.

2

Active Record makes it much easier to handle joins once you’ve set up the relations. You often don’t need to do that if you have suitable foreign key constraints in your database as gii and yiic will generate the relations for you.

Also, if you don’t need all of the columns, you can restrict the returned columns in your relation definition.

3

by qiang

Query builder is usefull for big select queries with a lot of data, not just only joins. AR in other hand make joins easier like Keith said. You can try it by yourself by make two queries - one with AR and another with DAO. I like AR idea to make some inserts and updates or simple selects, but for big selects I use only DAO.

4

I would suggest using AR first. You can do joins in AR, see Relational Active Record. If it proves too slow then your options are:

  • use eager loading of required related records,

  • move any filtering logic into the criteria, so only the required records are returned (don’t bring everyting back from db then filter),

  • optimise what is returned using select (you probably don’t need all fields),

  • use DAO.

I do use DAO in places, but optimised AR is close in performance and easier to write/ maintain (joins and conditions are written once and available in all queries, as relations and scopes).

One word of warning: where you have multiple scopes or scopes on related records, care must be taken to apply the same conditions in DAO/ QueryBuilder as in your AR scopes/ related conditions. It is easy to get different results.

That said, if performance is key and you are familiar with SQL then DAO is better.

5

thanks for all replies