How to write custom sql or query in magento

February 12th, 2011

Table names and table prefixes


$tableName = Mage::getSingleton('core/resource')
->getTableName('catalog_product_entity');
// if prefix was 'mage_' then the below statement
// would print out mage_catalog_product_entity
echo $tableName;
———————————

Accessing the database connection resource


$read = Mage::getSingleton('core/resource')->getConnection('core_read');
$write = Mage::getSingleton('core/resource')->getConnection('core_write');
——————————————————–
For a list of functions available, copy the following code into a Magento template.
$read = Mage::getSingleton('core/resource')->getConnection('core_read');
echo '<pre>';
print_r(get_class_methods($read));
echo '</pre>';
exit;
————————————————————-

Reading data from the database

$read = Mage::getSingleton('core/resource')->getConnection('core_read');
$query = 'SELECT * FROM ' . Mage::getSingleton('core/resource')->getTableName('catalog_product_entity');
$results = $read->fetchAll($query);
print_r($results);
——————————————————————

Writing information to the database

 

$write = Mage::getSingleton('core/resource')->getConnection('core_write');
// Add your own query below
// I didn't add one as I didn't want you to run the code
// and me break your database!
$query = 'add your query here';
$write->query($query);
http://fishpig.co.uk/direct-sql-queries-magento/

Looking for function setJoinCategories(true)

February 12th, 2011

If you are looking for setJoinCategories(true) then no need to search because it is not written anywhere. Just go below of page and look for following function
protected function _preparePage()
{
if(!$this->getJoinCategories()) {
return parent::_preparePage();
}

$this->getCollection()->getSelect()->reset(Zend_Db_Select::GROUP);

parent::_preparePage();

$collection = $this->getCollection();
$collection
->joinField(‘category_id’,
‘catalog/category_product’,
‘category_id’,
‘product_id=entity_id’,
null,
‘left’);
$category_name_attribute_id = Mage::getModel(‘eav/entity_attribute’)->loadByCode(‘catalog_category’, ‘name’)->getId();

//@nelkaake -m 13/11/10: Added support for tables with prefixes
$ccev_t = Mage::getConfig()->getTablePrefix(). ‘catalog_category_entity_varchar’;
$collection
->joinField(‘categories’,
$ccev_t,
‘GROUP_CONCAT(_table_categories.value)’,
‘entity_id=category_id’,
“_table_categories.attribute_id={$category_name_attribute_id}”,
‘left’);
$collection
->joinField(‘category’,
$ccev_t,
‘value’,
‘entity_id=category_id’,
“_table_category.attribute_id={$category_name_attribute_id}”,
‘left’);
$collection->groupByAttribute(‘entity_id’);

}

You can find everything here.

How to get table prefixes in magento

February 12th, 2011

Mage::getConfig()->getTablePrefix() you can use this value to get table prefixes

For example Mage::getConfig()->getTablePrefix(). ‘catalog_category_entity_varchar’;

also you can use below code to write custom query

$write = Mage::getSingleton(‘core/resource’)->getConnection(‘core_write’);

ITLive Web Services

January 16th, 2011

ITLive Web Services provides wide verity of development in PHP with custom and open source (Magento, Oscommerce, Joomla and wordpress).


Copyright © 2013 ITLive Web Services. All Rights Reserved.
No computers were harmed in the 0.771 seconds it took to produce this page.

Designed/Developed by Lloyd Armbrust & hot, fresh, coffee.