How to add field select query as field in Magento Collection -

How to add field select query as field in Magento Collection -

i have magento collection called mymodule/class. have 2 tables class , student in magento db.

class table:

id clas_name

student table:

id class_id student_name

class_id in pupil table foreign key of id in class table.

i want create mymodule/class collection sql looks below:

select a.*, (select count(id) studetn class_id = a.id) student_count class

or, want create mymodule/class above sql.

how suggestion?

looks have add together columns in query. in scenario have collection, allow assume $collection variable like:

$collection = mage::getmodel("mymodule/class")->getcollection()

so, have modify query like:

$collection->getselect()->columns( array( 'student_count' => new zend_db_expr('(select count(id) pupil class_id=main_table.id)' ));

above look generate query wrote.

magento