When I’ve been working with my project i tried to create sql syntax, that will allow me to select sum of votes from table.
Simple table join didn’t work as i expect, because it duplicated row values few times. So i had to join table created by select.
And here comes my question. Is it possible to do this in ActiveRecord? Below is example code of what I am talking about.
[sql]JOIN ( SELECT fid, SUM(pos) as pos, SUM(neg) as neg FROM rates GROUP by fid ) rates ON ( rates.fid = t.fid )[/sql]
$models = Model::model->findAll( array( 'select'=>'t.*, rates.*', 'join'=>'( SELECT fid, SUM(pos) as pos, SUM(neg) as neg FROM rates GROUP by fid ) rates ON ( rates.fid = t.fid )') );
but you need to add attributes related to ‘rates’ columns in your model, so they will be filled with this query:
class Model extends CActiveRecord {
public $pos;
public $neg;
...
Thank you very much! 
How about defining it in relations? Is this possible too? I see that I can do this using CDbCriteria too, but it would be really nice(and i think a little bit easier) defining it in relation rules 
relation needs another ActiveRecord model on other side so it is necessary to create activerecord model for SQL query instead of table… quite interesting, but never tried such approach.
Well, in theory it is possible, but the point is that Active Record, while creating sql query, wraps table name into [html]table_name[/html]. If it would be possible to skip that graves, Active Record would possibly read SELECT statement as table.
The question is: how to trick it?
you could try creating view - this should do the trick
I am not entirely sure if I understood you properly. Could you, please, describe it wider and little bit more clearly? Thank you in advance!
I’m also posting below a code, which returned me error “table couldn’t be found in database”.
class Rates extends CActiveRecord {
public static function model($className = __CLASS__) {
return parent::model($className);
}
public function tableName() {
return '( SELECT fid, SUM(pos) as pos, SUM(neg) as neg FROM rates GROUP by fid )';
}
}
create view in database:
create view my_view AS SELECT fid, SUM(pos) as pos, SUM(neg) as neg FROM rates GROUP by fid;
then use ‘my_view’ as table name in tableName function. Views are supported by most db engines which are supporting subqueries
I thought that I had to do something in ActiveRecord code 
Thank you for your help. Now it works really fine.
Also I think Yii should somehow support joining SELECT statements, but this matter isn’t for this thread.