id (int)
amount (float) //positive and negative
when (datetime)
reason (text)
I want to fetch:
the sum of amounts of all rows
the most recent transaction made and
the biggest absolute amount in a transaction.
I use the following MySQL statements respectively:
'SELECT SUM(`amounts`) FROM `transaction` WHERE 1'
'SELECT `when` FROM `transaction` ORDER BY `when` DESC'
'SELECT MAX(ABS(`amount`)) FROM `transaction` WHERE 1'
In the TransactionController I decided (for no particular reason) to use
Transaction::model()->findBySql(‘specific SQL statement’) for each of those SQL statements.
In detail I used (each of those in different parts of the controller):
$resultRow1 = Transaction::model()->findBySql('SELECT SUM(`amounts`) FROM `transaction` WHERE 1')
...
$resultRow2 = Transaction::model()->findBySql('SELECT `when` FROM `transaction` ORDER BY `when` DESC')
...
$resultRow3 = Transaction::model()->findBySql('SELECT MAX(ABS(`amount`)) FROM `transaction` WHERE 1')
So, while in the second one I don’t face any problem as I am able to access the result through
$resultRow->when
in the other two I can’t access the result. In what way can I accomplish this since I can’t use $resultRow->sum(amount) or $resultRow->max(abs(amount))?
Also, is it better to do this kind of calculations using the MySQL built-in functions or through PHP code?
Inside of the Transaction ActiveRecord class you have to define
public $sum;
public $max;
Now you should be able to access $model->sum.
Note that you are currently using ActiveRecord which returns model objects, but you can also use DAO which is faster and returns arrays. For example you can do:
$sum = Yii::app()->db->createCommand("SELECT SUM(`amounts`) as `sum` FROM `transaction` WHERE 1")->queryScalar();
echo $sum;
Also take a look at the query builder which was added recently.
It depends what you want to do with the results of a sql command. Please take a look at this page to see the benefits and actual usage cases of the ActiveRecord pattern.
You might use DAO/QueryBuilder:
if you have a case you can only hardly solve with ActiveRecord
if you need better performance for a certain sql command
if you just don’t need any model object(s) for a certain sql command (e.g. in a console application)
I think that when the table uses CRUD operations, using ActiveRecord is the only rational solution, if you do not want to spend days of your life coding.
In the application I am developing, besides the aforementioned table transaction, I also have a table named status where I store the current status of the application. This table has fields:
id (int)
name (varchar)
value(varchar)
I have preinserted the rows:
- name:balance, value:(whatever value that is renewed whenever a transaction is created/deleted)
- name:last transaction on, value:(again whatever datetime the last transaction happened)
- name:biggest transaction amount, value:(respective to the above)
In the case of transaction, I wouldn’t use anything less than ActiveRecord, because of the user input checks that I have to write code for.
On the other hand, in the case of the status table, I am currently using ActiveRecord but I could do otherwise as the inserting and updating are done by me and not by user input. So I could just make it with plain sql in the TransactionController.