Using the database features of the Yii framework

Question

I recently started working with Yii PHP MVC Framework. I'm looking for advice on how should I continue working with the database through the framework: should I use framework's base class CActiveRecord which deals with the DB, or should I go with the classic SQL query functions (in my case mssql)?

Obviously or not, for me it seems easier to deal with the DB through classic SQL queries, but, at some point, I imagine there has to be an advantage in using framework's way. Some SQL queries will get pretty complex pretty often. I just can't comprehend how the framework could help me and not make things more complicated than they actually are.

Answer 1

Why to use Yii:

Just imagine that you have many modules and for each module you have to write a pagination code; writing in old fashion style, will need a lot of time;

Why not use Yii ClistView widget? Oh, and this widget comes with a bonus: the data provider and the auto checking for the existance of the article that is about to be printed;

When using Yii CListView with results from ... Sphinx search engine, the widget will check if the article do really exists, because the result may not be correct

How long will it take for you to write a detection code for non existing registration?

And when you have different types of projects will you addapt the methods?

NO! Yii does this for you.

How long would it take for you to write the code in crud style ? create, read, update, delete ?

Are you going to adapt the old code from another project ?

Yii has a miracle module, called Gii, that generates models, modules, forms, controllers, the crud ... and many more

at first it might seem hard, but when you get experienced, it's easy

Answer 2

In any language when dealing with the database a framework can help you by providing an abstraction over the database.

Here is a scenario I know I found myself in many times during my earlier development days:

1. I have an application that needs a database.
2. I write a ton of code.
3. I put the SQL statements in the code along with everything else.
4. The database changes somehow.
5. I'm stuck with having to go back and make 100 changes to all my SQL statements.

It's very frustrating.

Another scenario I found:

1. I write a ton of code against a database.
2. Bugs come in. Lots of bugs. I can't figure them all out.
3. I'm asked to write tests for my code.
4. This is impossible because all my code relies on a direct implementation of the database. How do you test SQL statements when they're with the actual code?

So my advice is to use the framework because it can provide an abstraction over the database. This gives you two really big advantages:

1. You can potentially swap out the database later and your code stays the same! If you're using interfaces/some framework, then most likely you're dealing with objects and not SQL statements directly. A given implementation might know how to write to MySQL or SQL Server, but in general your code just says "Write this object", "Read that list."

2. You can test your code! A good framework that deals with data will let you mock the database so you can test it easily.

Try to avoid writing SQL statements directly in the application. It'll save you pain later.

Answer 3

Very General rule from my experience with Yii and massive databases:

Use Yii Active Record when:

1. You want to retrieve and post single to a few rows in the database (e.g. user changing his/her settings, updating users balance, adding a vote, getting a count of users online, getting the number of posts under a topic, checking if a model exists)
2. You want to rapidly design a hierarchical model structure between your tables, (e.g. $user->info->email,$user->settings->currency) allowing you to quickly adjust displayed currency/settings per use.

Stay away from Yii Active Record when:

1. You want to update several 100 records at a time. (too much overhead for the model)

   Yii::app()->db->command()  
   

   allows you to avoid the heavy objects and retrieves data in simple arrays.

2. You want to do advanced joins and queries that involve multiple tables.

3. Any batch job!! (e.g. checking a payments table to see which customers are overdue on their payments, updating database values etc.)

I love Yii Active Record, but I interchange between the Active Record Model and plain SQL (using Yii::app()->db) based on the requirement in the application.

At the end I have the option whether I want to update a single users currency

$user->info->currency = 'USD';
$user->info->save();

or if I want to update all users currencies:

Yii::app()->db->command('UPDATE ..... SET Currency="USD" where ...');

Answer 4

I'm unfamiliar with the database system bundled with Yii, but would advise you to use it a little bit to start with. My experience is with Propel, a popular PHP ORM. In general, ORM systems have a class per table (Propel has three per table).

Now, there'll probably be a syntax to do lookups and joins etc, but the first thing to do is to work out how to use raw SQL in your queries (for any of the CRUD operations). Put methods to do these queries in your model classes, so at least you will be benefitting from centralisation of code.

Once you've got that working, you can migrate to the recommended approach at a later time, without getting overwhelmed with the amount of material you have to learn in one go. Learning Yii (especially how to share code amongst controllers, and to write maintainable view templates) takes a while, so it may be sensible not to over-complicate it with many other things as well.

Answer 5

I would suggest you should use CActiveRecord.It will give many advantages -

1. You can use many widgets within yii directly as mentioned above.(For paginations,grids etc)

2. The queries which are generated by the Yii ORM are highly optimized.

3. You dont need to put the results extracted from SQLs in your VO objects.

4. If the tables for some reason modified(addition/deletion of column,changing data type), you just need to regenerate the models using the tool provided by yii.Just make sure you try to avoid doing any code changes in the models generated by yii, that will save your merging efforts.

5. If you plan to change the DB from MYSQL to other vendor in futur, it would be just config change for you.

6. Also you and your team would save your precious development time.