Magento2开发教程十三-数据库操作

调试数据集 Select

$collection = $this->_objectManager->get( 'Magento\Catalog\Model\ProductFactory' )->create()->getCollection();
echo $collection->load()->getSelectSql( true );

通过 Select 获取数据

$conn = $this->_objectManager->get( 'Magento\Framework\App\ResourceConnection' )->getConnection();
$tblMain = $conn->getTableName( 'cms_page' );
$tblStore = $conn->getTableName( 'cms_page_store' );

$select = $conn->select()
        ->distinct()
        ->from( [ 'page' => $tblMain ], [ 'page_id', 't' => 'title' ] )
        ->join( [ 'store' => $tblStore ], 'store.page_id = page.page_id', [ 'sid' => 'store_id' ] )
        ->where( 'is_active = ?', '1' )
        ->order( 'title ASC' )
        ->limit( 5, 1 );

$data = $conn->fetchAll( $select );

聚合查询（SUM）

$conn = $this->_objectManager->get( 'Magento\Framework\App\ResourceConnection' )->getConnection();
$tbl = $conn->getTableName( 'sales_order_item' );

// 所有记录总计
$select = $conn->select()
        ->from( $tbl, [ 'total' => new \Zend_Db_Expr( 'SUM( qty_ordered )' ) ] )
        ->where( 'order_id = ?', '1' );
$result = $conn->fetchOne( $select );

// 各局部统计
$select = $conn->select()
        ->from( $tbl, [ 'order_id', 'total' => new \Zend_Db_Expr( 'SUM( qty_ordered )' ) ] )
        ->group( 'order_id' )
        ->having( 'order_id != ?', '1' );
$result = $conn->fetchAll( $select );

更新数据

/** @var $conn \Magento\Framework\App\ResourceConnection */
/** @var $storeId int */
/** @var $ids array */

$tbl = $conn->getTableName( 'sales_order_item' );

// 插入数据
$conn->insert( $tbl, [ 'field_one' => $fieldOne, 'field_two' => $fieldTwo, 'field_three' => $fieldThree ] );

// 插入数据，碰到已存在的记录（primary / unique）则只更新指定的字段
$conn->insertOnDuplicate( $tbl, [ 'field_one' => $fieldOne, 'field_two' => $fieldTwo, 'field_three' => $fieldThree ], [ 'field_three' ] );

// 获取插入后的ID
$resId = $connection->lastInsertId();

// 更新数据
$conn->update( $tbl, [ 'field_one' => $fieldOne, 'field_two' => $fieldTwo ], $conn->quoteInto( 'store_id IN (?)', $storeId ) );

// 删除数据
$conn->delete( $tbl, [ 'id IN (?)' => $ids ] );

Model

/* @var \Magento\Directory\Model\ResourceModel\Region\Collection $collection */
$collection = $objectManager->get('Magento\Directory\Model\ResourceModel\Region\Collection');
$collection->getSelect()
    ->where('main_table.region_id = ?', 1)
    ->where('main_table.country_id=?', 'US');
foreach($collection as $row) {
    echo $row->getData('default_name');
}

Entity collection

常用方法

$collection->addAttributeToFilter($field, [ 'in' => $arr ]);
$collection->addAttributeToSort($field, 'desc');
$collection->addStoreFilter();
$collection->addUrlRewrite();
$collection->setPageSize( 10 );
$collection->addAttributeToSelect($field);

带field的查询

Entity collection有查询field的能力，只要查询条件里跟field有关，就会自动把field所在的表join到查询中

$collection->addAttributeToSelect($field, 'left'); // 等同于select xx as field ... left join xxx
$collection->addAttributeToFilter($field, 'field > 0')// 等同于where field > 0
$collection->setOrder($field, 'ASC'); // 等同于order by xxx ASC

特价置顶

special price是个特别的个案，特价需要在有效期间生效，有效期外不生效

$collection->addAttributeToSelect('special_from_date', 'left');
$collection->addAttributeToSelect('special_to_date', 'left');
$collection->addAttributeToSelect('special_price', 'left');
$collection->getSelect()->order('IF(special_from_date < now() AND special_to_date > now(), special_price, 0) DESC');

用户自定义增删改查

Insert

$themeId=3;
$this->_resources = \Magento\Framework\App\ObjectManager::getInstance()
->get('Magento\Framework\App\ResourceConnection');
$connection= $this->_resources->getConnection();

$themeTable = $this->_resources->getTableName('theme');
$sql = "Insert into " . $themeTable . "(theme_id,theme_path) Values (" . $themeId . ",'webmull/christmastheme' )";
$connection->query($sql);

Update

$themeId=3;
$this->_resources = \Magento\Framework\App\ObjectManager::getInstance()
->get('Magento\Framework\App\ResourceConnection');
$connection= $this->_resources->getConnection();

$themeTable = $this->_resources->getTableName('theme');
$sql = "Update " . $themeTable . " set theme_path='webmull/christmastheme' WHERE theme_id = " . $themeId . ";";
$connection->query($sql);

Delete

$themeId=3;
$this->_resources = \Magento\Framework\App\ObjectManager::getInstance()
->get('Magento\Framework\App\ResourceConnection');
$connection= $this->_resources->getConnection();

$themeTable = $this->_resources->getTableName('theme');
$sql = "DELETE FROM " . $themeTable . " WHERE theme_id = " . $themeId . ";";
$connection->query($sql);

Select

$themeId=3;

$this->_resources = \Magento\Framework\App\ObjectManager::getInstance()
->get('Magento\Framework\App\ResourceConnection');
$connection= $this->_resources->getConnection();

$themeTable = $this->_resources->getTableName('theme');
$sql = "Select * FROM " . $themeTable . " WHERE theme_id = " . $themeId . ";";
$connection->fetchAll($sql);

更多操作

实例一

$connection = $this->getConnection();
$select = $connection->select()->from(
	['main_table' => $this->getMainStoreTable($category->getStoreId())],
	['main_table.entity_id', 'main_table.name']
)->joinLeft(
	['url_rewrite' => $this->getTable('url_rewrite')],
	'url_rewrite.entity_id = main_table.entity_id AND url_rewrite.is_autogenerated = 1'
	. $connection->quoteInto(' AND url_rewrite.store_id = ?', $category->getStoreId())
	. $connection->quoteInto(' AND url_rewrite.entity_type = ?', CategoryUrlRewriteGenerator::ENTITY_TYPE),
	['request_path' => 'url_rewrite.request_path']
)->where(
	'main_table.entity_id IN (?)',
	array_reverse(explode(',', $category->getPathInStore()))
);

实例二

$connection = $this->getConnection();
$ratingCodeCond = $connection->getIfNullSql('title.value', 'main_table.rating_code');
$this->getSelect()->joinLeft(
	['title' => $this->getTable('rating_title')],
	$connection->quoteInto('main_table.rating_id=title.rating_id AND title.store_id = ?', (int)$storeId),
	['rating_code' => $ratingCodeCond]
);
return $this;