Sum a virtual attribute from related table

Hi All,

I am having trouble with a sum from a virtual column from a related table.

Training:

-id_T

-name

Appointment:

-id_A

-id_T (fk)

Register:

-id_R

-id_A (fk)

-id_P (fk)

Participant:

-id_P

-name

In Appointment I have a virtual attribute "test" where I count how many participants are registered in each appointment:




$registered_sql="(select count(id_P) from Register r where r.id_A=t.id_A)";

$criteria->select=array(

		'*',

		$registered_sql.' as test'

	);



Now I want to have an attribute in Training, where I will show the number of participants for each Training.

How do I make a sum from that virtual attribute "test" in table Training?

Thank you in advance.

In Training I want to have an attribute "total_nr" and I want to do something like:




$total_sql="(select sum(here I want to select the test attribute) from Appointment a where a.id_T=t.id_T)";

$criteria->select=array(

		'*',

		$total_sql.' as total_nr'

	);



How can I do it when it is not an actual column in the table?

You may declare a public variable $total_nr in Training model.




public $total_nr;



Then the query will automatically fill it with the result of the total count which you’ve selected as ‘total_nu’, if you are working with CActiveRecord.

Hi softark,

Thank you for your reply.

But my problem is that I don’t know how to complete this query:


select sum(******) from Appointment a where a.id_T=t.id_T

because what I want to sum up, are some values of a column of another table…

but this column itself is a virtual attribute…not a real column in that table…

Ah, I see.

So a Training has many Appointments, and an Appointment has many Registers.

An Appointment has a virtual attribute of the count of Registers, and you wanted to sum up the count of Registers in a Training … is that right?

Then you can do something like:




select count(r.id_R) from Appointment a LEFT JOIN Register r on r.id_A = a.id_A where a.id_T = t.id_T) 



Perfect!

Thank you softark :)