How to add a field selection request to the Magento Collection field

Question

I have a collection of Magento called mymodule/class. I have two tables classand studentin my Magento db.

Class table:

id     clas_name

Student table:

id   class_id    student_name

class_idthe student table has a foreign key idin the class table.

I want to make the collection mymodule/classso that sql looks like this:

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

Or, I want to do mymodule/classsql on top.

how to make any suggestions?

Answer 1

It looks like you need to add columns to your query. In the script, you already have a collection, let's say your $ collection variable, for example:

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

So you need to change the request as:

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

The above expression will generate a query as you wrote.

Answer 2

 Amit_Custommodule.xml at app/etc/modules/ 

<?xml version="1.0"?>
<config>
    <modules>
        <Amit_Custommodule>
            <codePool>community</codePool>
            <active>true</active>
        </Amit_Custommodule>
    </modules>
</config>

Path Of config.xml - //​​/Amit/Custommodule/etc/

codepool - , app/code/community

Amit - , Custommodule -

<?xml version="1.0" ?>
<config>
    <modules>
        <Amit_Custommodule>
            <version>1.0.0</version>
        </Amit_Custommodule>
    </modules>
    <global>
        <models>
            <custommodule>
                <class>Amit_Custommodule_Model</class>
                <resourceModel>custommodule_resource</resourceModel>
            </custommodule>
            <custommodule_resource>
                <class>Amit_Custommodule_Model_Resource</class>
                <entities>
                    <custommodule>
                        <table>studetn</table>
                    </custommodule>
                </entities>
            </custommodule_resource>
        </models>

    </global>

   </config>

Custommodule.php app/code/community/Amit/Custommodule/Model

<?php
class Amit_Custommodule_Model_Custommodule extends Mage_Core_Model_Abstract
{
    public function _construct()
    {
        $this->_init('custommodule/custommodule');
    }

}

, Custommodule.php

- Custommodule.php app/code/community/Amit/Custommodule/Model/Resource/

<?php
class Amit_Custommodule_Model_Resource_Custommodule extends Mage_Core_Model_Resource_Db_Abstract
{
    /**
     * Initialize resource model
     *
     * @return void
     */
    public function _construct()
    {
        $this->_init('custommodule/custommodule', 'id');
    }
    public function getMyCount($std)
{
    $Table = Mage::getSingleton('core/resource')->getTableName('custommodule/custommodule');

    $select = $this->getReadConnection()->select()
        ->from(
            array('main_table' => $Table),
            array(new Zend_Db_Expr('COUNT(main_table.id)'))
        )
        ->where('main_table.id = :id');

    $bind = array('id' => (int)$std->getId());
    $counts = $this->getReadConnection()->fetchOne($select, $bind);

    return intval($counts);
}

}

Mage::getModel("custommodule/custommodule")->load($primaryKeyOfTable);

,

- Collection.php app/code/community/Amit/Custommodule/Model/Resource/Custommodule

<?php
class Amit_Custommodule_Model_Resource_Custommodule_Collection
extends Mage_Core_Model_Resource_Db_Collection_Abstract{
    protected function _constuct(){
        $this->_init('custommodule/custommodule');    
    }
}

$Collection=Mage::getModel("custommodule/custommodule")->Collection(); 

foreach($Collectio as $each)
{
$each->getMyCount();
}

http://www.amitbera.com/create-an-magento-extension-with-custom-database-table/