magento join table collection

Question

I'm customizing Magento FAQ extension for sort faq items by category.below collection is used to get all items active faq items.

$collection = Mage :: getModel('flagbit_faq/faq')->getCollection() 
              ->addStoreFilter(Mage :: app()->getStore())
              ->addIsActiveFilter();  

there is relation table "faq_category_item"

Table structure:-

category_id    faq_id
   1              1
   2              2
   1              3 

So I decide to join two tables.I unsuccess in that. What i tried is below.

$tbl_faq_item = Mage::getSingleton('core/resource')->getTableName('faq_category_item');

$collection = Mage :: getModel('flagbit_faq/faq')->getCollection() 
                  ->getSelect()
                  ->join(array('t2' => $tbl_faq_item),'main_table.faq_id = t2.faq_id','t2.category_id')  
                  ->addStoreFilter(Mage :: app()->getStore())
                  ->addIsActiveFilter();

Whats wrong in this and how can i filter the particular category items.Please share some good links to learn Magento model collections.

Thanks in advance

Answer 1

The returned type from getSelect() and join() is a select object, not the collection that addStoreFilter() and addIsActiveFilter() belong to. The select part needs to occur later in the chain:

$collection = Mage :: getModel('flagbit_faq/faq')->getCollection() 
              ->addStoreFilter(Mage :: app()->getStore())
              ->addIsActiveFilter();
// Cannot append getSelect right here because $collection will not be a collection
$collection->getSelect()
           ->join(array('t2' => $tbl_faq_item),'main_table.faq_id = t2.faq_id','t2.category_id');