Magento 2后台Admin Grid展示自定义SQL聚合数据实现方法

架构老林

2026-8-28

问题说明

已开发Magento 2自定义模块，产品相关业务数据存储在自定义数据表中
需求为在后台制作统计报表，需编写自定义查询关联客户表、产品表，实现按产品分组的count等聚合计算逻辑
核心疑问：是否支持直接传入自定义SQL查询得到的完整结果集至Admin Grid，由网格自动识别对应列名完成数据渲染？

现有UI Grid配置文件（XML）如下：

<?xml version="1.0"?>
<listing xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
         xsi:noNamespaceSchemaLocation="urn:magento:module:Magento_Ui:etc/ui_configuration.xsd">
    <argument name="data" xsi:type="array">
        <item name="js_config" xsi:type="array">
            <item name="provider" xsi:type="string">bargain_offer_listing.bargain_offer_listing_data_source</item>
            <item name="deps" xsi:type="string">bargain_offer_listing.bargain_offer_listing_data_source</item>
            <item name="spinner" xsi:type="string">bargain_offer_listing_columns</item>
        </item>
    </argument>
    <listingToolbar name="listing_top">
        <bookmark name="bookmarks"/>
        <columnsControls name="columns_controls"/>
        <filterSearch name="fulltext"/>
        <filters name="listing_filters"/>
        <paging name="listing_paging"/>
    </listingToolbar>
    <dataSource name="bargain_offer_listing_data_source">
        <argument name="dataProvider" xsi:type="configurableObject">
            <argument name="class" xsi:type="string">MyModule\Bargain\Ui\DataProvider\OfferListingProvider
            </argument>
            <argument name="name" xsi:type="string">bargain_offer_listing_data_source</argument>
            <argument name="primaryFieldName" xsi:type="string">entity_id</argument>
            <argument name="requestFieldName" xsi:type="string">entity_id</argument>
            <argument name="data" xsi:type="array">
                <item name="update_url" xsi:type="url" path="mui/index/render"/>
                <item name="storageConfig" xsi:type="array">
                    <item name="indexField" xsi:type="string">entity_id</item>
                </item>
            </argument>
        </argument>
    </dataSource>
    <columns name="bargain_offer_listing_columns">
        <column name="entity_id">
            <settings>
                <filter>textRange</filter>
                <label translate="true">ID</label>
            </settings>
        </column>
        <column name="product_sku">
            <settings>
                <filter>text</filter>
                <label translate="true">Product Sku</label>
            </settings>
        </column>
        <column name="product_name">
            <settings>
                <filter>text</filter>
                <bodyTmpl>ui/grid/cells/text</bodyTmpl>
                <label translate="true">Product Name</label>
            </settings>
        </column>
        <column name="number_of_buyers">
            <settings>
                <filter>text</filter>
                <label translate="true">No of buyers</label>
            </settings>
        </column>
        <column name="times_bought">
            <settings>
                <filter>text</filter>
                <label translate="true">Times Bought</label>
            </settings>
        </column>
    </columns>
</listing>

当前已创建的空数据提供类代码如下：

use Magento\Framework\View\Element\UiComponent\DataProvider\DataProvider;

class OfferListingProvider extends DataProvider
{

}

实现方案

Magento 2 Admin Grid完全支持直接传入自定义SQL查询的结果集完成渲染，不需要绑定对应的Model、Collection，只要返回的数据结构符合要求，字段名和XML中定义的列name属性完全一致，就能自动完成渲染。

具体实现只需要重写OfferListingProvider的getData()方法即可，该方法是UI Grid取数的核心入口，同时需要兼容分页、筛选、排序逻辑，避免后台Grid功能失效。

完整实现代码如下：

<?php
namespace MyModule\Bargain\Ui\DataProvider;

use Magento\Framework\Api\FilterBuilder;
use Magento\Framework\Api\Search\ReportingInterface;
use Magento\Framework\Api\Search\SearchCriteriaBuilder;
use Magento\Framework\App\RequestInterface;
use Magento\Framework\View\Element\UiComponent\DataProvider\DataProvider;
use Magento\Framework\App\ResourceConnection;
use Zend_Db_Expr;

class OfferListingProvider extends DataProvider
{
    protected $resource;

    public function __construct(
        $name,
        $primaryFieldName,
        $requestFieldName,
        ReportingInterface $reporting,
        SearchCriteriaBuilder $searchCriteriaBuilder,
        RequestInterface $request,
        FilterBuilder $filterBuilder,
        ResourceConnection $resource,
        array $meta = [],
        array $data = []
    ) {
        parent::__construct($name, $primaryFieldName, $requestFieldName, $reporting, $searchCriteriaBuilder, $request, $filterBuilder, $meta, $data);
        $this->resource = $resource;
    }

    public function getData()
    {
        // 获取Grid分页、排序参数
        $currentPage = (int)$this->request->getParam('p', 1);
        $pageSize = (int)$this->request->getParam('limit', 20);
        $sortField = $this->request->getParam('sort', 'entity_id');
        $sortDir = strtoupper($this->request->getParam('dir', 'DESC'));
        $offset = ($currentPage - 1) * $pageSize;

        $connection = $this->resource->getConnection();
        
        // 编写自定义统计SQL，替换为实际业务的关联、聚合逻辑即可
        $select = $connection->select()
            ->from(
                ['main_table' => $this->resource->getTableName('your_custom_bargain_table')],
                [
                    'entity_id' => 'main_table.entity_id',
                    'product_sku' => 'cpe.sku',
                    'product_name' => 'cpev.value',
                    // 聚合计算使用Zend_Db_Expr避免框架转义出错
                    'number_of_buyers' => new Zend_Db_Expr('COUNT(DISTINCT main_table.customer_id)'),
                    'times_bought' => new Zend_Db_Expr('COUNT(main_table.entity_id)')
                ]
            )
            ->joinLeft(
                ['cpe' => $this->resource->getTableName('catalog_product_entity')],
                'main_table.product_id = cpe.entity_id',
                []
            )
            ->joinLeft(
                ['cpev' => $this->resource->getTableName('catalog_product_entity_varchar')],
                'cpe.entity_id = cpev.entity_id AND cpev.attribute_id = (SELECT attribute_id FROM ' . $this->resource->getTableName('eav_attribute') . ' WHERE attribute_code = "name" AND entity_type_id = 4) AND cpev.store_id = 0',
                []
            )
            ->group('main_table.product_id');

        // 处理Grid筛选参数，根据实际需要的筛选项扩展即可
        $filters = $this->request->getParam('filters', []);
        if (!empty($filters['product_sku'])) {
            $select->where('cpe.sku LIKE ?', '%' . trim($filters['product_sku']) . '%');
        }
        if (!empty($filters['entity_id']['from'])) {
            $select->where('main_table.entity_id >= ?', (int)$filters['entity_id']['from']);
        }
        if (!empty($filters['entity_id']['to'])) {
            $select->where('main_table.entity_id <= ?', (int)$filters['entity_id']['to']);
        }

        // 查询总条数，用于分页组件渲染
        $totalSelect = clone $select;
        $totalSelect->reset(\Zend_Db_Select::COLUMNS)
            ->reset(\Zend_Db_Select::GROUP)
            ->columns(new Zend_Db_Expr('COUNT(DISTINCT main_table.product_id)'));
        $totalRecords = (int)$connection->fetchOne($totalSelect);

        // 追加排序、分页规则
        $select->order($sortField . ' ' . $sortDir)
            ->limit($pageSize, $offset);

        $items = $connection->fetchAll($select);

        // 返回结构必须符合UI组件要求，Grid会自动匹配items中的字段和XML定义的列渲染
        return [
            'totalRecords' => $totalRecords,
            'items' => $items
        ];
    }
}

关键注意点

XML中<column name="xxx">的name值必须和SQL查询返回的字段名完全一致，不需要额外做字段映射
这种直接写原生SQL的方式最适合统计报表类的复杂聚合场景，比通过Collection拼接join查询性能更高，也不需要额外创建Model、Collection层
聚合函数、自定义表达式必须用Zend_Db_Expr包裹，避免框架自动给字段加引号导致SQL语法错误
如果不需要筛选、排序功能，可以删掉对应参数处理逻辑，直接返回全量查询结果，但建议保留分页逻辑避免数据量过大导致加载缓慢。

内容的提问来源于stack exchange，提问作者Huzaifa Mustafa

Magento 2后台Admin Grid展示自定义SQL聚合数据实现方法

关键注意点 #

关键注意点