How to convert sql query to magento in order to fetch data

Question

I am developing a custom module which will enhance the magento reviews and rating, now i have created a custom table which will count votes on reviews(facebook comment like).

Now i have to convert the following sql query into magento query format, but i don't know how can i do this.

SELECT review_id, COUNT(*) FROM reviews GROUP BY review_id, votes HAVING review_id=(SELECT review_id FROM reviews WHERE vote_id='<7>') AND votes='<1>'

point to remember is that, i have to do this using event observer not in models

• Julien Lachal: since Magento is based on Zend, I suggest you have a look at this

Answer 1

Custom Queries work like these:

$write = Mage::getSingleton('core/resource')->getConnection('core_write');
// now $write is an instance of Zend_Db_Adapter_Abstract
$readresult=$write->query("SELECT *
FROM `tableName`
ORDER BY `anyField` DESC
LIMIT 0 , 30 ");
while ($row = $readresult->fetch() ) {
$Ids[]=$row['id'];
}

//Further you can insert like

foreach ($Ids as $entity_id) {
$write->query( 'INSERT INTO cataloginventory_stock_item
(`product_id`,`stock_id`,`qty`,`is_in_stock`)
VALUES ( '.$_id.', 1, 99999, 1)' );
} 

You can alter the query as per you need.