Convert a sql query to magento

Question

I have two queries,

1.

{
 $select = $this->_getReadAdapter()->select()->from(array('main_table' => $this->getMainStoreTable($category->getStoreId())), array('main_table.entity_id', 'main_table.name'))->where('main_table.entity_id IN (?)', array_reverse(explode(',', $category->getPathInStore())));
 if ($isActive) {
 $select->where('main_table.is_active = ?', '1');
 }
 $select->order('main_table.path ASC');
}

And then 2:

{
 $select->joinLeft(array('url_rewrite' => $this->_resource->getTableName('core/url_rewrite')), 'url_rewrite.category_id=main_table.entity_id AND url_rewrite.is_system=1 AND ' .$this->_connection->quoteInto('url_rewrite.store_id = ? AND ', (int)$storeId) .$this->_connection->prepareSqlCondition('url_rewrite.id_path', array('like' => 'category/%')), array('request_path' => 'url_rewrite.request_path'));
}

Now in this join, I want to add use index for join(index). So is it possible to form such queries in Magento some how so that resulting query can look like -

{
 SELECT `main_table`.`entity_id`, `main_table`.`name`, `url_rewrite`.`request_path` FROM `catalog_category_flat_store_2` AS `main_table` LEFT JOIN `core_url_rewrite` AS `url_rewrite` USE INDEX FOR JOIN (unq_core_url_rewrite_id_path_is_system_store_id) ON url_rewrite.category_id = main_table.entity_id AND url_rewrite.is_system = 1 AND url_rewrite.store_id = 2 AND url_rewrite.id_path LIKE 'category/%'
 WHERE ( main_table.entity_id IN ( '10', '35' ) ) AND ( main_table.is_active = '1' ) ORDER BY `main_table`.`path` ASC;
}

Answer 1

As there is no resource model available for the table 'catalog_category_flat_store_2', You need to do following way:

$resource = Mage::getSingleton('core/resource');
$readConnection = $resource->getConnection('core_read');
$query = "SELECT `main_table`.`entity_id`, 
 `main_table`.`name`, 
 `url_rewrite`.`request_path` 
FROM `catalog_category_flat_store_2` AS `main_table` 
 LEFT JOIN `core_url_rewrite` AS `url_rewrite` FORCE INDEX FOR JOIN ( 
 unq_core_url_rewrite_id_path_is_system_store_id 
 ) 
 ON url_rewrite.category_id = main_table.entity_id 
 AND url_rewrite.is_system = 1 
 AND url_rewrite.store_id = 2 
 AND url_rewrite.id_path LIKE 'category/%'
WHERE ( main_table.entity_id IN ( '10', '35' ) ) 
 AND ( main_table.is_active = '1' ) 
ORDER BY `main_table`.`path` ASC";
$results = $readConnection->fetchAll($query);
//var_dump($results);

If you want to make it collection object then:

$collection = new Varien_Data_Collection();
foreach($results as $row){
 $rowObj = new Varien_Object();
 $rowObj->setData($row);
 $collection->addItem($rowObj);
}
//now you can get the data using collection way
foreach($collection as $_data){
 print_r($_data->getData());
}

• Priyanka Saini: Actually these are two different queries. And this join is added in this query with $select->leftJoin(); Is it possible some how to add use index for join (index) in left join?