MySQL functions in model()->findBySql()

Hi guys and girls,

I have a table named transaction with fields:




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?

Do


SELECT SUM(`amounts`) as `sum` ...

and


SELECT MAX(ABS(`amount`)) as `max` ...

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.

Well, That was impressive.

So in ActiveRecord I can access:

  • the fields of the table which are implicitly declared as well as

  • other variable declarations I make through MySQL ‘as <variable>’ statements since they are declared as member variables in the model class.

Am I right?

Wow, lots of conventions buddy…

Anyway, what do you suggest I use when I make more advanced MySQL statements? ActiveRecord find() or plain old SQL queries?

Thanx a lot

Yes correct.

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.

That was very helpful of yours.