调试数据集 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' ] );
// 更新数据
$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');
}
原文链接：https://blog.csdn.net/joe_develope/article/details/78094478
