Magento中直接使用SQL語句

sumi2005 2014-03-27

展開全文

Magento中直接使用SQL語句

原理：

magento是基于Zend Framework的,所以底層用的還是zend的zend db

在文件app/code/core/Mage/Catalog/model/Resource/Eav /Mysql4/Config.php 中追蹤到下面的函數(shù) getAttributesUsedInListing()

Php代碼

/**
* Retrieve Product Attributes Used in Catalog Product listing
*
* @return array
*/
public function getAttributesUsedInListing() {
$select = $this->_getReadAdapter()->select()
->from(array(’main_table’ => $this->getTable(’eav/attribute’)))
->join(
array(’additional_table’ => $this->getTable(’catalog/eav_attribute’)),
‘main_table.attribute_id = additional_table.attribute_id’,
array()
)
->joinLeft(
array(’al’ => $this->getTable(’eav/attribute_label’)),
‘a(chǎn)l.attribute_id = main_table.attribute_id AND al.store_id = ‘ . (int) $this->getStoreId(),
array(’store_label’ => new Zend_Db_Expr(’IFNULL(al.value, main_table.frontend_label)’))
)
->where(’main_table.entity_type_id=?’, $this->getEntityTypeId())
->where(’additional_table.used_in_product_listing=?’, 1);
– $sql = $select->assemble();
– echo $sql;
return $this->_getReadAdapter()->fetchAll($select);
}

Magento操作數(shù)據(jù)庫是在 Zend DB（Zend Framework）的基礎(chǔ)上簡單的做了下封裝了。Zend DB 有自己的一套規(guī)則,，來組合生成最終的SQL查詢語句,，可以看到上面的代碼中有 from() join() joinLeft() where() 等函數(shù)，亂七八糟的一大堆東西,，需要對 Zend DB的規(guī)則非常熟悉,，才能知道實際執(zhí)行的SQL語句，有沒有辦法直接打印出SQL語句？找了下,，還真有,，就是assemble()函數(shù)。在上面代碼中最后部分可以看到,。順被把SQL也附上來

Sql代碼

SELECT `main_table`.*,
IFNULL(al.value, main_table.frontend_label) AS `store_label`
FROM `eav_attribute` AS `main_table`
INNER JOIN `catalog_eav_attribute` AS `additional_table`
ON main_table.attribute_id = additional_table.attribute_id
LEFT JOIN `eav_attribute_label` AS `al`
ON al.attribute_id = main_table.attribute_id AND al.store_id = 1
WHERE (main_table.entity_type_id=’4′)
AND (additional_table.used_in_product_listing=1)

Magento中打印SQL語句來調(diào)試

有時為了調(diào)試magento商城系統(tǒng),需要獲取當(dāng)前的查詢sql語句,magento中獲取SQL語句,這里我們通過

$collection->getSelectSql(true)來調(diào)試sql

Php代碼

$collection=Mage::getResourceModel('reports/product_collection');
$query=$collection->getSelectSql(true);
echo $query;

magento獲取SQL語句的另外一種方法是設(shè)置打印SQL為true

Php代碼

$collection=Mage::getResourceModel('reports/product_collection');
$collection->printlogquery(true);

得到的SQL語句 ：

Sql代碼

SELECT `e`.* FROM `catalog_product_entity` AS `e`

這里只是打印查詢產(chǎn)品的SQL,如果要獲取其他地方的SQL語句,道理也是一樣的,我們根據(jù)上面的sql語句可以看到,其實magento的性能很差,"select *",magetno又是基于EAV架構(gòu)的,可以想象下這速度

操作：

Magento的Models 和Collection 很強大,，使用它們可以很方便的查詢和操作數(shù)據(jù)庫。但是有些場合,，因為一些特殊需求或?qū)agento的了解不夠深,，可能會需要自己手寫SQL語句來查詢和操作數(shù)據(jù)庫。以下分別是讀寫數(shù)據(jù)庫的代碼,。

Php代碼

// For Read
// fetch read database connection that is used in Mage_Core module
$read= Mage::getSingleton('core/resource')->getConnection('core_read');
// first way
$query = $read->query("select name from core_website");
while ($row = $query->fetch())
{
$row = new Varien_Object($row);
echo "" . $row->getName() . " ";
}
// second way
$results = $read->fetchAll("SELECT * FROM core_website;");
foreach ($results as $row)
{
echo $row['name'] . " ";
}

Php代碼

// For Write
// fetch write database connection that is used in Mage_Core module
$write = Mage::getSingleton('core/resource')->getConnection('core_write');
// now $write is an instance of Zend_Db_Adapter_Abstract
$write->query("insert into tablename values ('aaa','bbb','ccc')");

注意上面的getConnection()方法中的參數(shù) "core_read",，表明了Magento將要使用的資源。與之相對應(yīng),，當(dāng)我們修改數(shù)據(jù)庫的時候使用參數(shù)"core_write".一般情況下 getConnection方法的參數(shù)應(yīng)設(shè)成"core_read" 或 "core_write"(應(yīng)該不指定也是可以的,，但是如果Magento有多個數(shù)據(jù)庫就必須指定了)。

作為新的entension module,在config.xml對"core_read" "core_write" 進行定義是個好的習(xí)慣,。定義如下：

Xml代碼

<config>
<global>
<resources>
<extension_setup>
<connection>
<use>core_setup</use>
</connection>
</extension_setup>
<extension_read>
<connection>
<use>core_read</use>
</connection>
</extension_read>
<extension_write>
<connection>
<use>core_write</use>
</connection>
</extension_write>
</resources>
</global>
</config>

對應(yīng)上面新增的module的名字.使用下面相對應(yīng)的語句在read或write Database:

Php代碼

$conn = Mage::getSingleton('core/resource')->getConnection('extension_read');
$conn = Mage::getSingleton('core/resource')->getConnection('extension_write');

一般情況是絕大多數(shù)的module都定義成"core_read" "core_write"方便且節(jié)省資源,。當(dāng)然特殊情況除外：

給每個module不同的讀寫權(quán)限
需要用多個Database

實例：

Php代碼

<?php
/**
* Get the resource model
*/
$resource = Mage::getSingleton('core/resource');
/**
* Retrieve the read connection
*/
$readConnection = $resource->getConnection('core_read');
/**
* Retrieve the write connection
*/
$writeConnection = $resource->getConnection('core_write');

Get a table name from a string

Php代碼

<?php
/**
* Get the resource model
*/
$resource = Mage::getSingleton('core/resource');
/**
* Get the table name
*/
$tableName = $resource->getTableName('catalog_product_entity');
/**
* if prefix was 'mage_' then the below statement
* would print out mage_catalog_product_entity
*/
echo $tableName;

Get a table name from an entity name

Php代碼

<?php
/**
* Get the resource model
*/
$resource = Mage::getSingleton('core/resource');
/**
* Get the table name
*/
$tableName = $resource->getTableName('catalog/product');
/**
* if prefix was 'mage_' then the below statement
* would print out mage_catalog_product_entity
*/
echo $tableName;

Reading From The Database

Varien_Db_Select::fetchAll

This method takes a query as it's parameter, executes it and then returns all of the results as an array. In the code example below, we use Varien_Db_Select::fetchAll to return all of the records in the catalog_product_entity table.

Php代碼

<?php
/**
* Get the resource model
*/
$resource = Mage::getSingleton('core/resource');
/**
* Retrieve the read connection
*/
$readConnection = $resource->getConnection('core_read');
$query = 'SELECT * FROM ' . $resource->getTableName('catalog/product');
/**
* Execute the query and store the results in $results
*/
$results = $readConnection->fetchAll($query);
/**
* Print out the results
*/
echo sprintf('<pre>%s</pre>' print_r($results, true));

Varien_Db_Select::fetchCol

This method is similar to fetchAll except that instead of returning all of the results, it returns the first column from each result row. In the code example below, we use Varien_Db_Select::fetchCol to retrieve all of the SKU's in our database in an array.

Php代碼

<?php
/**
* Get the resource model
*/
$resource = Mage::getSingleton('core/resource');
/**
* Retrieve the read connection
*/
$readConnection = $resource->getConnection('core_read');
/**
* Retrieve our table name
*/
$table = $resource->getTableName('catalog/product');
/**
* Execute the query and store the results in $results
*/
$sku = $readConnection->fetchCol('SELECT sku FROM ' . $table . ');
/**
* Print out the results
*/
echo sprintf('<pre>%s</pre>' print_r($results, true));

Try this code and look at the results. Notice how all of the SKU's are in a single array, rather than each row having it's own array? If you don't understand this, try changing fetchCol for fetchAll and compare the differences.

Varien_Db_Select::fetchOne

Unlike the previous two methods, Varien_Db_Select::fetchOne returns one value from the first row only. This value is returned on it's own and is not wrapped in an array. In the code example below, we take a product ID of 44 and return it's SKU.

Php代碼

<?php
/**
* Get the resource model
*/
$resource = Mage::getSingleton('core/resource');
/**
* Retrieve the read connection
*/
$readConnection = $resource->getConnection('core_read');
/**
* Retrieve our table name
*/
$table = $resource->getTableName('catalog/product');
/**
* Set the product ID
*/
$productId = 44;
$query = 'SELECT sku FROM ' . $table . ' WHERE entity_id = '
. (int)$productId . ' LIMIT 1';
/**
* Execute the query and store the result in $sku
*/
$sku = $readConnection->fetchOne($query);
/**
* Print the SKU to the screen
*/
echo 'SKU: ' . $sku . ' ';

When trying out this example, ensure you change the product ID to an ID that exists in your database!

You may think that fetchOne works the same as fetchCol or fetchAll would if you only added 1 column to the SELECT query and added a 'LIMIT 1', however you would be wrong. The main difference with this function is that the value returned is the actual value, where as Varien_Db_Select::fetchCol and Varien_Db_Select::fetchAll would wrap the value in an array. To understand this a little, try swapping the method's and comparing the results.

Writing To The Database

When saving a Magento model, there can be a lot of background data being saved that you weren't even aware of. For example, saving a product model can take several seconds due to the amount of related data saves and indexing that needs to take place. This is okay if you need all the data saving, but if you only want to update the SKU of a product, this can be wasteful.

The example code below will show you how when given a product ID, you can alter the SKU. This is a trivial example but should illustrate how to execute write queries against your Magento database.

Php代碼

<?php
/**
* Get the resource model
*/
$resource = Mage::getSingleton('core/resource');
/**
* Retrieve the write connection
*/
$writeConnection = $resource->getConnection('core_write');
/**
* Retrieve our table name
*/
$table = $resource->getTableName('catalog/product');
/**
* Set the product ID
*/
$productId = 44;
/**
* Set the new SKU
* It is assumed that you are hard coding the new SKU in
* If the input is not dynamic, consider using the
* Varien_Db_Select object to insert data
*/
$newSku = 'new-sku';
$query = "UPDATE {$table} SET sku = '{$sku}' WHERE entity_id = "
. (int)$productId;
/**
* Execute the query
*/
$writeConnection->query($query);

To test this has worked, use the knowledge gained from the first part of this tutorial to write a query to extract the SKU that has just been changed.

Varien_Db_Select

The Varien_Db_Select, which has been touched on in this article is a far better option for extracting/wriiting information. Not only is it easy to use, it also provides a layered of security, which if used correctly, is impenetrable. More will be covered on Varien_Db_Select (aka Zend_Db_Select) in a future article.