title: Magento join query query using 3 table
body: <p>I am new to magento. I just want to frame a query, but i am struggling to do.</p>
  
  <p>My mysql query:</p>
  
  <pre><code>  SELECT `main_table`.*, `t1`.*, count(t2.review_id) AS `totalrecord`,    SUM(t2.rating) AS `totalrating`, `t2`.* FROM `nbmp_vendor` AS `main_table` 
  LEFT JOIN `customer_entity` AS `t1` ON main_table.customer_id = t1.entity_id 
   LEFT JOIN `nbmp_review` AS `t2` ON main_table.customer_id = t2.vendor_id 
   WHERE ((vendor_status = '1')) AND (t1.group_id = '4') group by main_table.vendor_id  
  </code></pre>
  
  <p>I have tried in magento:</p>
  
  <pre><code>    &lt;?php 
    class Blazedream_TopVendors_Block_Monblock extends Mage_Core_Block_Template {
  
   public function methodblock() {
  
      $myTable = "customer_entity";
      $myTable1 = "nbmp_review";
      $collection  = Mage::getModel('topvendors/topvendors')-&gt;getCollection()
                              -&gt;addFieldToFilter(array('vendor_status'), array('1'))
                              -&gt;setOrder('vendor_id','asc');
  
      $collection-&gt;getSelect()
              -&gt;joinLeft(array("t1" =&gt; $myTable), "main_table.customer_id = t1.entity_id")
              -&gt;where("t1.group_id = '4'");
  
      $collection-&gt;getSelect()
              -&gt;columns('count(t2.review_id) AS totalrecord')
              -&gt;columns('SUM(t2.rating) AS totalrating')
              -&gt;joinLeft(array("t2" =&gt; $myTable1), "main_table.customer_id = t2.vendor_id")
              -&gt;group("main_table.vendor_id");
      echo $collection-&gt;getselect(); die;
          $ddata = $collection-&gt;getData();
         $i = 0;
         foreach($ddata as $data)
         {
  
         $retour[$i]['id'] = $data-&gt;getData('vendor_id');
         $retour[$i]['name'] = $data-&gt;getData('vendor_name');
         $retour[$i]['vendor_slug'] = $data-&gt;getData('vendor_slug');
         // $retour[$i]['rating_avg'] = $vendors[$data-&gt;getData('vendor_id')]['rating_avg'];
         $retour[$i]['totalrating'] = $data-&gt;getData('totalrating');
         $retour[$i]['rating_avg'] = $data-&gt;getData('totalrating') / $data-&gt;getData('totalrecord');
         $i++;
  
       }
      // Mage::getSingleton('adminhtml/session')-&gt;addSuccess('Cool Ca marche !!');
     return $retour;
  }
  }
  </code></pre>
  
  <p>I am getting only error. </p>
  
  <pre><code> Fatal error: Call to undefined method Blazedream_TopVendors_Model_Mysql4_TopVendors_Collection::group() in magento
  </code></pre>
  
  <p>Can anyone help me to form this??
  Thanks in advance.</p>