A data grid is usually used to visualize some data coming from a data source. OroDataGridBundle allows for use of various data sources for data grids, and includes the ORM data source adapter out of the box. It is possible to implement your own data source adapters<\/a> as well.<\/p>\n The ORM data source types allow for database query specification, sorters and filters definitions to be specified in the data grid configuration. Data grid configuration can be supplied by a developer in YAML format. By convention, the datagrid.yml files placed in Resources\/config folders of any application bundle are processed automatically. All supported data source configuration options that can be used in data source configuration section are described in the documentation<\/a>.<\/p>\n Data grids in Oro applications are highly customizable. It is possible to modify an existing grid in order to fetch more data than was originally defined in the grid configuration. In this article, we will retrieve and present to a user some additional data in the existing products-grid<\/a>.<\/p>\n And before we start customizing it, let’s take a deeper look into two aspects of how the data grids actually work:<\/p>\n DatagridBuilder class is responsible for creating and configuring a data grid object and its data source. This is how the build method is processing the grid configuration internally:<\/p>\n Imagine that you need to show a list of related price lists for every product record in the product grid. You want it to be displayed in a separate column with a multi-select filter. Also you want to add one more column to display owner of each of the product records.<\/p>\n One of the possible ways to customize this grid would be through events triggered by the system during the grid build process and when the data is fetched from the data source.<\/p>\n There are several events triggered before processing the data grid configuration files. In this case, a\u00a0good choice is the onBuildBefore event. By listening to this event you can add new elements to the grid configuration or modify already existing configuration in your event listener.<\/p>\n More information about grid column definition configuration options is available in the documentation<\/a>.<\/p><\/blockquote>\n The Product entity has a many-to-one relation with the Business Unit entity, so in order to add the owner column to the grid and load the owner data from the data source, you should modify its query configuration by adding additional join and select parts.<\/p>\n However, the retrieving data for the price lists column is a little bit more complicated, because the Product entity has many-to-many relation with price lists, and the join result will contain duplicate rows. In such situations or when some other dynamic data should be included into the query results, a possible solution would be data modification after the rows were fetched from the data source.<\/p>\n This is how the data retrieval works in general:<\/p>\n So in our customization, we will fetch the price list data in a separate query, and then we will attach this data to each of the product records in the grid.<\/p>\n The resulting implementation of the ProductsGridListener may look similar to this example:<\/p>\n We will need to register this event listener in the service container:<\/p>\n After the application cache is refreshed (or immediately in the dev mode) two new columns will appear in the product grid.<\/p>\n Our second customization task will be to add filters for the newly introduced column.<\/p>\n In most cases, the built-in filters<\/a> would work just perfectly. But in the case of the price lists column, a custom filter is required. The purpose of this filter will be to modify the data retrieval query depending on the filter values entered by a user.<\/p>\n Our new filter should be registered in the service container with the oro_filter.extension.orm_filter.filter tag:<\/p>\n This filter can be added to the grid configuration similarly to how we added new columns – in an event listener. Thus the final implementation of the ProductsGridListener would look like this:<\/p>\n A fully working example, organized into a custom bundle is attached. In order to add this bundle to your application please extract the content of the zip archive into a source code directory that is recognized by your composer autoload settings.<\/p>\n Download<\/span><\/a>\n\n Please let us know if you find this article useful. We hope it can help the developers in the OroCommerce community to further improve user experience and make dealing with data even more enjoyable and fun.<\/p>\n![\"Customizing](\"https:\/\/oroinc.com\/b2b-ecommerce\/wp-content\/uploads\/sites\/3\/2016\/07\/grid-example-1.png\")
<\/a><\/p>\nData Sources<\/h3>\n
Inner Workings of Data Grids<\/h3>\n
\n
Building Grids<\/h4>\n
![\"OroDataGridBundle](\"https:\/\/oroinc.com\/b2b-ecommerce\/wp-content\/uploads\/sites\/3\/2016\/07\/datagrid_base_uml.jpg\")
<\/a><\/p>\n![\"Data](\"https:\/\/oroinc.com\/b2b-ecommerce\/wp-content\/uploads\/sites\/3\/2016\/07\/build-flow.png\")
<\/a><\/p>\nFetching Data<\/h4>\n
![\"Data](\"https:\/\/oroinc.com\/b2b-ecommerce\/wp-content\/uploads\/sites\/3\/2018\/01\/orm-result-1.png\")
<\/a><\/p>\nProduct Grid Customization<\/h3>\n
class ProductsGridListener\r\n{\r\n ... \r\n\r\n \/**\r\n * @param BuildBefore $event\r\n *\/\r\n public function onBuildBefore(BuildBefore $event)\r\n {\r\n $datagridConfiguration = $event->getConfig();\r\n $this->addBusinessUnitColumn($datagridConfiguration);\r\n $this->addPriceListsColumn($datagridConfiguration);\r\n }\r\n\r\n \/**\r\n * @param OrmResultAfter $event\r\n *\/\r\n public function onResultAfter(OrmResultAfter $event)\r\n {\r\n $records = $event->getRecords();\r\n $this->addPriceListsToRecords($records);\r\n }\r\n\r\n \/**\r\n * @param DatagridConfiguration $datagridConfiguration\r\n *\/\r\n protected function addPriceListsColumn(DatagridConfiguration $datagridConfiguration)\r\n {\r\n $column = [\r\n 'label' => 'Price Lists',\r\n 'type' => 'twig',\r\n 'template' => 'OroB2BPricingBundle:Datagrid:Column\/price_lists.html.twig',\r\n 'frontend_type' => 'html',\r\n 'renderable' => true,\r\n ];\r\n $datagridConfiguration->addColumn('price_lists', $column);\r\n }\r\n\r\n \/**\r\n * @param DatagridConfiguration $datagridConfiguration\r\n *\/\r\n protected function addBusinessUnitColumn(DatagridConfiguration $datagridConfiguration)\r\n {\r\n $datagridConfiguration->joinTable(\r\n 'left',\r\n [\r\n 'join' => BusinessUnit::class,\r\n 'alias' => 'business_unit',\r\n 'conditionType' => 'WITH',\r\n 'condition' => 'product.owner = business_unit',\r\n ]\r\n );\r\n\r\n $column = [\r\n 'label' => 'Owner'\r\n ];\r\n\r\n \/\/ column name should be ther same as the field alias in the select query\r\n $datagridConfiguration->addColumn('owner', $column, 'business_unit.name as owner');\r\n }\r\n\r\n \/**\r\n * @param ResultRecord[] $records\r\n * @throws DoctrineORMORMException\r\n *\/\r\n protected function addPriceListsToRecords(array $records)\r\n {\r\n $repository = $this->registry->getRepository(PriceListToProduct::class);\r\n \/** @var EntityManager $objectManager *\/\r\n $objectManager = $this->registry->getManager();\r\n\r\n $products = [];\r\n foreach ($records as $record) {\r\n $products[] = $objectManager->getReference(Product::class, $record->getValue('id'));\r\n }\r\n\r\n $priceLists = [];\r\n foreach ($repository->findBy(['product' => $products]) as $item) {\r\n $priceLists[$item->getProduct()->getId()][] = $item->getPriceList();\r\n }\r\n\r\n \/** @var ResultRecord $record *\/\r\n foreach ($records as $record) {\r\n $id = $record->getValue('id');\r\n $products[] = $objectManager->getReference(Product::class, $id);\r\n\r\n $record->addData(['price_lists' => $priceLists[$id]]);\r\n }\r\n }\r\n}\r\n<\/pre>\ngrid_event_listener.product:\r\n class: 'OroBundleCustomGridBundleDatagridProductsGridListener'\r\n arguments:\r\n - @doctrine\r\n tags:\r\n - { name: kernel.event_listener, event: oro_datagrid.datagrid.build.before.products-grid, method: onBuildBefore }\r\n - { name: kernel.event_listener, event: oro_datagrid.orm_datasource.result.after.products-grid, method: onResultAfter }\r\n<\/pre>\nCustom Filters<\/h3>\n
class ProductPriceListsFilter extends EntityFilter\r\n{\r\n \/**\r\n * @var RegistryInterface\r\n *\/\r\n protected $registry;\r\n\r\n \/**\r\n * @inheritdoc\r\n *\/\r\n public function apply(FilterDatasourceAdapterInterface $ds, $data)\r\n {\r\n \/** @var array $data *\/\r\n $data = $this->parseData($data);\r\n if (!$data) {\r\n return false;\r\n }\r\n\r\n $this->restrictByPriceList($ds, $data['value']);\r\n\r\n return true;\r\n }\r\n\r\n \/**\r\n * @param RegistryInterface $registry\r\n *\/\r\n public function setRegistry(RegistryInterface $registry)\r\n {\r\n $this->registry = $registry;\r\n }\r\n\r\n \/**\r\n * @param OrmFilterDatasourceAdapter|FilterDatasourceAdapterInterface $ds\r\n * @param array $priceLists\r\n *\/\r\n public function restrictByPriceList($ds, array $priceLists)\r\n {\r\n $queryBuilder = $ds->getQueryBuilder();\r\n $parentAlias = $queryBuilder->getRootAliases()[0];\r\n $parameterName = $ds->generateParameterName('price_lists');\r\n\r\n $repository = $this->registry->getRepository(PriceListToProduct::class);\r\n $subQueryBuilder = $repository->createQueryBuilder('relation');\r\n $subQueryBuilder->where(\r\n $subQueryBuilder->expr()->andX(\r\n $subQueryBuilder->expr()->eq('relation.product', $parentAlias),\r\n $subQueryBuilder->expr()->in('relation.priceList', \":$parameterName\")\r\n )\r\n );\r\n\r\n $queryBuilder->andWhere($subQueryBuilder->expr()->exists($subQueryBuilder->getQuery()->getDQL()));\r\n $queryBuilder->setParameter($parameterName, $priceLists);\r\n }\r\n}\r\n<\/pre>\ngrid_filter.price_lists:\r\n class: 'OroBundleCustomGridBundleFilterProductPriceListsFilter'\r\n public: false\r\n arguments:\r\n - '@form.factory'\r\n - '@oro_filter.filter_utility'\r\n calls:\r\n - [setRegistry, ['@doctrine']]\r\n tags:\r\n - { name: oro_filter.extension.orm_filter.filter, type: product-price-lists }\r\n<\/pre>\nclass ProductsGridListener\r\n{\r\n \/**\r\n * @var RegistryInterface\r\n *\/\r\n protected $registry;\r\n\r\n \/**\r\n * @param RegistryInterface $registry\r\n *\/\r\n public function __construct(RegistryInterface $registry)\r\n {\r\n $this->registry = $registry;\r\n }\r\n\r\n \/**\r\n * @param BuildBefore $event\r\n *\/\r\n public function onBuildBefore(BuildBefore $event)\r\n {\r\n $datagridConfiguration = $event->getConfig();\r\n $this->addBusinessUnitColumn($datagridConfiguration);\r\n $this->addPriceListsColumn($datagridConfiguration);\r\n $this->addPriceListsFilter($datagridConfiguration);\r\n }\r\n\r\n \/**\r\n * @param OrmResultAfter $event\r\n *\/\r\n public function onResultAfter(OrmResultAfter $event)\r\n {\r\n $records = $event->getRecords();\r\n $this->addPriceListsToRecords($records);\r\n }\r\n\r\n \/**\r\n * @param DatagridConfiguration $datagridConfiguration\r\n *\/\r\n protected function addPriceListsColumn(DatagridConfiguration $datagridConfiguration)\r\n {\r\n $column = [\r\n 'label' => 'Price Lists',\r\n 'type' => 'twig',\r\n 'template' => 'OroCustomGridBundle:Datagrid:Column\/price_lists.html.twig',\r\n 'frontend_type' => 'html',\r\n 'renderable' => true,\r\n ];\r\n $datagridConfiguration->addColumn('price_lists', $column);\r\n }\r\n\r\n \/**\r\n * @param DatagridConfiguration $datagridConfiguration\r\n *\/\r\n protected function addBusinessUnitColumn(DatagridConfiguration $datagridConfiguration)\r\n {\r\n $datagridConfiguration->joinTable(\r\n 'left',\r\n [\r\n 'join' => BusinessUnit::class,\r\n 'alias' => 'business_unit',\r\n 'conditionType' => 'WITH',\r\n 'condition' => 'product.owner = business_unit',\r\n ]\r\n );\r\n\r\n $column = [\r\n 'label' => 'Owner'\r\n ];\r\n\r\n \/\/ column name should be ther same as the field alias in the select query\r\n $datagridConfiguration->addColumn('owner', $column, 'business_unit.name as owner');\r\n }\r\n\r\n \/**\r\n * @param DatagridConfiguration $datagridConfiguration\r\n *\/\r\n protected function addPriceListsFilter(DatagridConfiguration $datagridConfiguration)\r\n {\r\n $filter = [\r\n 'type' => 'product-price-lists',\r\n 'data_name' => 'price_lists',\r\n 'options' => [\r\n 'field_type' => 'entity',\r\n 'field_options' => [\r\n 'class' => PriceList::class,\r\n 'property' => 'name',\r\n 'multiple' => true\r\n ]\r\n ]\r\n ];\r\n\r\n $datagridConfiguration->addFilter('price_lists', $filter);\r\n }\r\n\r\n \/**\r\n * @param ResultRecord[] $records\r\n * @throws DoctrineORMORMException\r\n *\/\r\n protected function addPriceListsToRecords(array $records)\r\n {\r\n $repository = $this->registry->getRepository(PriceListToProduct::class);\r\n \/** @var EntityManager $objectManager *\/\r\n $objectManager = $this->registry->getManager();\r\n\r\n $products = [];\r\n foreach ($records as $record) {\r\n $products[] = $objectManager->getReference(Product::class, $record->getValue('id'));\r\n }\r\n\r\n $priceLists = [];\r\n foreach ($repository->findBy(['product' => $products]) as $item) {\r\n $priceLists[$item->getProduct()->getId()][] = $item->getPriceList();\r\n }\r\n\r\n \/** @var ResultRecord $record *\/\r\n foreach ($records as $record) {\r\n $id = $record->getValue('id');\r\n $products[] = $objectManager->getReference(Product::class, $id);\r\n\r\n $record->addData(['price_lists' => $priceLists[$id]]);\r\n }\r\n }\r\n}\r\n<\/pre>\n