raw aql query in framework with Active Record pattern based ORM

Question

I use yii framework that implements Active Record pattern as ORM base. It has CActiveRecord class that is a table wrapper class with attributes reflecting table columns. So each object of this class represents a database row.

Wiki says about Active Record pattern:

Active record is an approach to accessing data in a database

and

A database table or view is wrapped into a class. Thus, an object instance is tied to a single row in the table.

So far so good.

But where should I put complex raw sql query that retrieves statistics data for example?

And, more generally, where should I put methods that retrieve some data that can not be an active record object (like data retrieved with aggregation queries) or if I knowing do not want to retrieve an object but an array instead for example?

Answer 1

I'd suggest that you should create a separate class for that purpose. If you put all this logic in an active record class, very soon it will grow in size quite prominently.

The second step is not to use yii framework, since active record is the worst of all ORM patterns. Data-mapper pattern is much better since it separates logic and database queries.

The third step is not to use ORM at all since it is an anti-pattern itself.

Answer 2

The active record pattern doesn't cover the usage of complex SQL queries. To allow that the active record pattern should be combined with a pattern that provides access to the database tables like the repository pattern. An example of that would be Doctrine 1.x that has two different classes for representing a row and a table.

Since Yii provides only one model class I can assume that these method should be implemented in the CActiveRecord class. It already implements a bunch of find methods, so I think it's totally acceptable.