How to add field select query as field in Magento Collection

Question

I have Magento collection called mymodule/class. I have two tables class and student in my Magento db.

Class table:

id     clas_name

Student table:

id   class_id    student_name

class_id in student table is foreign key of id in class table.

I want to make mymodule/class collection so that sql looks like below:

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

Or, I want to make mymodule/class from above sql.

how to do any suggestion?

Answer 1

Looks like you have to add columns in your query. In scenario you already have collection, let us assume your $collection variable like:

$collection = Mage::getModel("mymodule/class")->getCollection()

So, you have to modify query like:

$collection->getSelect()->columns(
        array(
            'student_count' => new Zend_Db_Expr('(SELECT count(id) FROM student WHERE class_id=main_table.id)'
        )));

Above expression will generate query as you wrote.