ITLive Web Services

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/

Posted in Magento | No Comments »

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.

Posted in Magento | No Comments »

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’);

Posted in Magento | No Comments »