Magento – How to select data from two table in magento with same condition in custom table

collection;magento-1.8sql

I have two table tbl_magazine, tbl_page ….
I need to select data form magazine like…
if magazine is enabled then also check magazine page is enable or not…
if magazine is enable and some pages of magazine is disable then only select acive table.

my table is
------------              

magazine  table

magazine_id
magazine_name
magazine_cover
status

page table


page_id
magazine_id
page_content
status

I musing this query..

   public function getMagazine() {
        $current_magazine_id = Mage::registry('current_magazine_id');
        $id = $current_magazine_id['o'];
        Mage::unregister('current_magazine_id');

        if ($id) {

            $collection = Mage::getModel('magazine/page')->getCollection()
                    ->addFieldToFilter('magazine_id', $id)
                    ->addFieldToSelect('status', array('eq' => 'ENABLED'))
                     ->setOrder('page_id', 'ASC');
        } else {
            $defaultMagazine = Mage::getModel('magazine/display')->getCollection();
            $data = $defaultMagazine->getData();
            $defaultMagazineId = $data[0]['magazine_id'];
            $collection = Mage::getModel('magazine/page')->getCollection()
                    ->addFieldToFilter('magazine_id', $defaultMagazineId)
                    ->addFieldToSelect('status', array('eq' => 'ENABLED'))
                     ->setOrder('page_id', 'ASC');
        }
        return $collection;
    }

my error

SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'FROM `magazine` AS `main_table` ORDER BY month ASC' at line 1

Best Answer

I used this and currently its working fine...

public function getMagazine() {
    $current_magazine_id = Mage::registry('current_magazine_id');
    $id = $current_magazine_id['o'];
    Mage::unregister('current_magazine_id');

    if ($id) {

        $collection = Mage::getModel('magazine/page')->getCollection()
                ->addFieldToSelect('*')
                ->addFieldToFilter('status', array('eq' => 'ENABLED'))
                ->addFieldToFilter('magazine_id', array('in' => $id));
        $collection->getSelect()->order('page_id ASC');
    } else {
        $defaultMagazine = Mage::getModel('magazine/display')->getCollection();
        $data = $defaultMagazine->getData();

        $defaultMagazineId = $data[0]['magazine_id'];
        $collection = Mage::getModel('magazine/page')->getCollection()
                ->addFieldToSelect('*')
                ->addFieldToFilter('status', array('eq' => 'ENABLED'))
                ->addFieldToFilter('magazine_id', array('in' => $defaultMagazineId));
        $collection->getSelect()->order('page_id ASC');
    }
    return $collection;
}
Related Topic