On this information, we’ll find out how we will create customized indexers for sluggish MySQL queries.
In Magento 2, indexers play an important function in enhancing the efficiency of your e-commerce retailer. They’re liable for indexing and organizing information to allow quicker search and retrieval of knowledge. Magento transforms information corresponding to merchandise, classes, buyer and so forth., to enhance the efficiency of your storefront utilizing indexers. Magento has a really refined structure that shops plenty of service provider information in lots of database tables. To optimize retailer efficiency, Magento shops the info into particular tables utilizing indexers.
Step 1: Create a brand new module utilizing the information.
Step 2: Lets create a easy API for retrieving the results of product attributes, like identify , amount, value utilizing MySQL queries.
<?xml model="1.0"?>
<routes xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xsi:noNamespaceSchemaLocation="urn:magento:module:Magento_Webapi:and so forth/webapi.xsd">
<route url="/V1/check/api/me" methodology="GET">
<service class="WebkulTestApiTestApiManagementInterface" methodology="getApiData"/>
<assets>
<useful resource ref="nameless"/>
</assets>
</route>
</routes>
<?php
namespace WebkulTestModel;
class TestApiManagement implements WebkulTestApiTestApiManagementInterface
{
const SEVERE_ERROR = 0;
const SUCCESS = 1;
const LOCAL_ERROR = 2;
protected $productCollectionFactory;
protected $attributeFactory;
protected $attributeCollectionFactory;
public perform __construct(
MagentoEavModelResourceModelEntityAttributeFactory $attributeFactory,
MagentoCatalogModelResourceModelProductCollectionFactory $productCollectionFactory,
WebkulTestModelResourceModelAttributeCollectionFactory $attributeCollectionFactory
) {
$this->productCollectionFactory = $productCollectionFactory;
$this->attributeFactory = $attributeFactory;
$this->attributeCollectionFactory = $attributeCollectionFactory;
}
/**
* get check Api information.
*
* @api
*
* @param int $id
*
*/
public perform getApiData($id)
{
$mannequin = $this->productCollectionFactory
->create();
$eavAttribute = $this->attributeFactory->create();
$productAttributeId = $eavAttribute->getIdByCode('catalog_product', 'identify');
$proPriceAttId = $eavAttribute->getIdByCode('catalog_product', 'value');
$proWeightAttId = $eavAttribute->getIdByCode('catalog_product', 'weight');
$catalogProductEntityVarchar = $model->getTable('catalog_product_entity_varchar');
$catalogProductEntityDecimal = $model->getTable('catalog_product_entity_decimal');
$cataloginventoryStockItem = $model->getTable('cataloginventory_stock_item');
$sql = $catalogProductEntityVarchar.' as cpev';
$cond = 'e.entity_id = cpev.entity_id';
$fields = ['product_name' => 'value'];
$model->getSelect()
->be part of($sql, $cond, $fields)
->the place('cpev.store_id = 0 AND cpev.attribute_id = '.$productAttributeId);
$sql = $catalogProductEntityDecimal.' as cped';
$cond = 'e.entity_id = cped.entity_id';
$fields = ['product_price' => 'value'];
$model->getSelect()
->be part of($sql, $cond, $fields)
->the place('cped.store_id = 0 AND (cped.attribute_id =
'.$proPriceAttId.')');
$model->getSelect()->be part of(
$cataloginventoryStockItem.' as csi',
'e.entity_id = csi.product_id',
["product_qty" => "qty"]
)->the place("csi.website_id = 0 OR csi.website_id = 1");
return $model->getData();
}
}
Now let’s find out how we will obtain the identical outcome utilizing customized indexer to lock the method.
Step 3: In your module’s and so forth listing, create a brand new XML file named your_index_name.xml.This file will outline the configuration to your customized indexer. Specify the identify, ID, and different settings to your indexer, corresponding to the info supply and the schedule for updating the index.
<?xml model="1.0" ?>
<config xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:noNamespaceSchemaLocation="urn:magento:framework:Indexer/and so forth/indexer.xsd">
<indexer id="product_attributes_indexer" class="WebkulTestModelIndexerProductAttributesIndexer" view_id="product_attributes_indexer" >
<title translate="true">Product Attributes Indexer</title>
<description translate="true">Indexes product attributes like identify, value, and amount</description>
</indexer>
</config>
A change log desk is created in response to the naming rule – INDEXER_TABLE_NAME + ‘_cl’, in case of product_attributes_indexer it will likely be product_attributes_indexer_cl. The desk incorporates the version_id auto-increment column and entity_id column that incorporates identifiers of entities to be re-indexed.
Step 4: In your module’s and so forth listing, create mview.xml which is used to trace database adjustments for a sure entity and operating change deal with (execute() methodology).
<?xml model="1.0" encoding="UTF-8"?>
<config xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:noNamespaceSchemaLocation="urn:magento:framework:Mview/and so forth/mview.xsd">
<view id="product_attributes_indexer" class="WebkulTestModelIndexerProductAttributesIndexer" group="indexer">
<subscriptions>
<desk identify="catalog_product_entity" entity_column="entity_id" />
</subscriptions>
</view>
</config>
Step 5: Implement the Indexer Class WebkulTestModelIndexerProductAttributesIndexer that extends the MagentoFrameworkIndexerAbstractProcessor class.
Principally, Indexer ought to have the ability to carry out with 3 kinds of motion :
- Row index : For single entity. executeRow($id) methodology might be name on this indexer course of.
- Checklist index : For processing set of entity. executeList(array $ids) methodology might be name on this indexer course of.
- Full index : For processing all entities from particular dictionary. executeFull() methodology might be name on this indexer course of.
<?php
namespace WebkulTestModelIndexer;
use MagentoFrameworkIndexerActionInterface;
use MagentoFrameworkMviewActionInterface as MviewActionInterface;
use MagentoFrameworkIndexerIndexerInterfaceFactory;
class ProductAttributesIndexer implements ActionInterface, MviewActionInterface
{
personal $indexerFactory;
public perform __construct(
IndexerInterfaceFactory $indexerFactory,
MagentoFrameworkAppResourceConnection $resourceConnection,
MagentoEavModelResourceModelEntityAttributeFactory $attributeFactory,
WebkulTestModelResourceModelAttribute $indexattribute
) {
$this->indexerFactory = $indexerFactory;
$this->useful resource = $resourceConnection;
$this->connection = $resourceConnection->getConnection();
$this->attributeFactory = $attributeFactory;
$this->productAttributesData = $indexattribute;
}
public perform execute($ids)
{
$indexer = $this->indexerFactory->create()->load('product_attributes_indexer');
if ($indexer->isInvalid()) {
return;
}
$connection = $this->connection;
$customTable = $this->productAttributesData->getMainTable();
$productTable = $connection->getTableName('catalog_product_entity');
$eavAttribute = $this->attributeFactory->create();
$productAttributeId = $eavAttribute->getIdByCode('catalog_product', 'identify');
$proPriceAttId = $eavAttribute->getIdByCode('catalog_product', 'value');
$proWeightAttId = $eavAttribute->getIdByCode('catalog_product', 'weight');
$catalogProductEntityVarchar = $connection->getTableName('catalog_product_entity_varchar');
$catalogProductEntityDecimal = $connection->getTableName('catalog_product_entity_decimal');
$cataloginventoryStockItem = $connection->getTableName('cataloginventory_stock_item');
$choose = $connection->choose()->from(['e' => $productTable], ['entity_id']);
if (!empty($ids)) {
$select->the place(
"e.entity_id IN(?)",
$ids
);
}
$sql = $catalogProductEntityVarchar.' as cpev';
$cond = 'e.entity_id = cpev.entity_id';
$fields = ['product_name' => 'value'];
$choose
->be part of($sql, $cond, $fields)
->the place('cpev.store_id = 0 AND cpev.attribute_id = '.$productAttributeId);
$sql = $catalogProductEntityDecimal.' as cped';
$cond = 'e.entity_id = cped.entity_id';
$fields = ['product_price' => 'value'];
$choose
->be part of($sql, $cond, $fields)
->the place('cped.store_id = 0 AND (cped.attribute_id =
'.$proPriceAttId.')');
$select->be part of(
$cataloginventoryStockItem.' as csi',
'e.entity_id = csi.product_id',
["product_qty" => "qty"]
)->the place("csi.website_id = 0 OR csi.website_id = 1");
$information = $connection->fetchAll($choose);
foreach ($information as $merchandise) {
$entityId = $merchandise['entity_id'];
$identify = $merchandise['product_name'];
$value = $merchandise['product_price'];
$amount = $merchandise['product_qty'];
//save information in your customized indexer desk
$connection->insertOnDuplicate($customTable, [
'product_id' => $entityId,
'name' => $name,
'price' => $price,
'qty' => $quantity
]);
}
}
public perform executeFull()
{
$this->execute([]);
}
public perform executeList(array $ids)
{
$this->execute($ids);
}
public perform executeRow($id)
{
$this->execute([$id]);
}
}
Step 6: Configure Dependency Injection To make sure that your customized indexer is correctly instantiated and used throughout the Magento system, it’s essential to configure dependency injection.
<kind identify="MagentoFrameworkIndexerConfigInterface">
<arguments>
<argument identify="indexers" xsi:kind="array">
<merchandise identify="product_attributes_indexer" xsi:kind="string">WebkulTestModelIndexerProductAttributesIndexer</merchandise>
</argument>
</arguments>
</kind>
Step7: Check and Confirm Upon getting carried out your customized indexer, it’s vital to completely check and confirm its performance. Run the required instructions to set off indexing and make sure that your information is correctly listed and searchable.
php bin/magento indexer:reindex product_attributes_indexer
Now let’s create the perform in api mannequin to make use of the above indexer outcome:
<?xml model="1.0"?>
<routes xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xsi:noNamespaceSchemaLocation="urn:magento:module:Magento_Webapi:and so forth/webapi.xsd">
<route url="/V1/check/api/index" methodology="GET">
<service class="WebkulTestApiTestApiManagementInterface" methodology="getIndexData"/>
<assets>
<useful resource ref="nameless"/>
</assets>
</route>
</routes>
public perform getIndexData()
{
/** WebkulTestModelResourceModelAttributeCollectionFactory */
$attributeCollection = $this->attributeCollectionFactory->create();
return $attributeCollection->getData();
}
As you possibly can test the results of each the method, it took half the time in fetching the outcome utilizing the customized indexers outcome.
We hope it would assist you to. Thanks!!
If any subject or doubt please be at liberty to say in remark part.
We’d be joyful to assist. Completely happy Coding!!
On this information, we’ll find out how we will create customized indexers for sluggish MySQL queries.
In Magento 2, indexers play an important function in enhancing the efficiency of your e-commerce retailer. They’re liable for indexing and organizing information to allow quicker search and retrieval of knowledge. Magento transforms information corresponding to merchandise, classes, buyer and so forth., to enhance the efficiency of your storefront utilizing indexers. Magento has a really refined structure that shops plenty of service provider information in lots of database tables. To optimize retailer efficiency, Magento shops the info into particular tables utilizing indexers.
Step 1: Create a brand new module utilizing the information.
Step 2: Lets create a easy API for retrieving the results of product attributes, like identify , amount, value utilizing MySQL queries.
<?xml model="1.0"?>
<routes xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xsi:noNamespaceSchemaLocation="urn:magento:module:Magento_Webapi:and so forth/webapi.xsd">
<route url="/V1/check/api/me" methodology="GET">
<service class="WebkulTestApiTestApiManagementInterface" methodology="getApiData"/>
<assets>
<useful resource ref="nameless"/>
</assets>
</route>
</routes>
<?php
namespace WebkulTestModel;
class TestApiManagement implements WebkulTestApiTestApiManagementInterface
{
const SEVERE_ERROR = 0;
const SUCCESS = 1;
const LOCAL_ERROR = 2;
protected $productCollectionFactory;
protected $attributeFactory;
protected $attributeCollectionFactory;
public perform __construct(
MagentoEavModelResourceModelEntityAttributeFactory $attributeFactory,
MagentoCatalogModelResourceModelProductCollectionFactory $productCollectionFactory,
WebkulTestModelResourceModelAttributeCollectionFactory $attributeCollectionFactory
) {
$this->productCollectionFactory = $productCollectionFactory;
$this->attributeFactory = $attributeFactory;
$this->attributeCollectionFactory = $attributeCollectionFactory;
}
/**
* get check Api information.
*
* @api
*
* @param int $id
*
*/
public perform getApiData($id)
{
$mannequin = $this->productCollectionFactory
->create();
$eavAttribute = $this->attributeFactory->create();
$productAttributeId = $eavAttribute->getIdByCode('catalog_product', 'identify');
$proPriceAttId = $eavAttribute->getIdByCode('catalog_product', 'value');
$proWeightAttId = $eavAttribute->getIdByCode('catalog_product', 'weight');
$catalogProductEntityVarchar = $model->getTable('catalog_product_entity_varchar');
$catalogProductEntityDecimal = $model->getTable('catalog_product_entity_decimal');
$cataloginventoryStockItem = $model->getTable('cataloginventory_stock_item');
$sql = $catalogProductEntityVarchar.' as cpev';
$cond = 'e.entity_id = cpev.entity_id';
$fields = ['product_name' => 'value'];
$model->getSelect()
->be part of($sql, $cond, $fields)
->the place('cpev.store_id = 0 AND cpev.attribute_id = '.$productAttributeId);
$sql = $catalogProductEntityDecimal.' as cped';
$cond = 'e.entity_id = cped.entity_id';
$fields = ['product_price' => 'value'];
$model->getSelect()
->be part of($sql, $cond, $fields)
->the place('cped.store_id = 0 AND (cped.attribute_id =
'.$proPriceAttId.')');
$model->getSelect()->be part of(
$cataloginventoryStockItem.' as csi',
'e.entity_id = csi.product_id',
["product_qty" => "qty"]
)->the place("csi.website_id = 0 OR csi.website_id = 1");
return $model->getData();
}
}
Now let’s find out how we will obtain the identical outcome utilizing customized indexer to lock the method.
Step 3: In your module’s and so forth listing, create a brand new XML file named your_index_name.xml.This file will outline the configuration to your customized indexer. Specify the identify, ID, and different settings to your indexer, corresponding to the info supply and the schedule for updating the index.
<?xml model="1.0" ?>
<config xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:noNamespaceSchemaLocation="urn:magento:framework:Indexer/and so forth/indexer.xsd">
<indexer id="product_attributes_indexer" class="WebkulTestModelIndexerProductAttributesIndexer" view_id="product_attributes_indexer" >
<title translate="true">Product Attributes Indexer</title>
<description translate="true">Indexes product attributes like identify, value, and amount</description>
</indexer>
</config>
A change log desk is created in response to the naming rule – INDEXER_TABLE_NAME + ‘_cl’, in case of product_attributes_indexer it will likely be product_attributes_indexer_cl. The desk incorporates the version_id auto-increment column and entity_id column that incorporates identifiers of entities to be re-indexed.
Step 4: In your module’s and so forth listing, create mview.xml which is used to trace database adjustments for a sure entity and operating change deal with (execute() methodology).
<?xml model="1.0" encoding="UTF-8"?>
<config xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:noNamespaceSchemaLocation="urn:magento:framework:Mview/and so forth/mview.xsd">
<view id="product_attributes_indexer" class="WebkulTestModelIndexerProductAttributesIndexer" group="indexer">
<subscriptions>
<desk identify="catalog_product_entity" entity_column="entity_id" />
</subscriptions>
</view>
</config>
Step 5: Implement the Indexer Class WebkulTestModelIndexerProductAttributesIndexer that extends the MagentoFrameworkIndexerAbstractProcessor class.
Principally, Indexer ought to have the ability to carry out with 3 kinds of motion :
- Row index : For single entity. executeRow($id) methodology might be name on this indexer course of.
- Checklist index : For processing set of entity. executeList(array $ids) methodology might be name on this indexer course of.
- Full index : For processing all entities from particular dictionary. executeFull() methodology might be name on this indexer course of.
<?php
namespace WebkulTestModelIndexer;
use MagentoFrameworkIndexerActionInterface;
use MagentoFrameworkMviewActionInterface as MviewActionInterface;
use MagentoFrameworkIndexerIndexerInterfaceFactory;
class ProductAttributesIndexer implements ActionInterface, MviewActionInterface
{
personal $indexerFactory;
public perform __construct(
IndexerInterfaceFactory $indexerFactory,
MagentoFrameworkAppResourceConnection $resourceConnection,
MagentoEavModelResourceModelEntityAttributeFactory $attributeFactory,
WebkulTestModelResourceModelAttribute $indexattribute
) {
$this->indexerFactory = $indexerFactory;
$this->useful resource = $resourceConnection;
$this->connection = $resourceConnection->getConnection();
$this->attributeFactory = $attributeFactory;
$this->productAttributesData = $indexattribute;
}
public perform execute($ids)
{
$indexer = $this->indexerFactory->create()->load('product_attributes_indexer');
if ($indexer->isInvalid()) {
return;
}
$connection = $this->connection;
$customTable = $this->productAttributesData->getMainTable();
$productTable = $connection->getTableName('catalog_product_entity');
$eavAttribute = $this->attributeFactory->create();
$productAttributeId = $eavAttribute->getIdByCode('catalog_product', 'identify');
$proPriceAttId = $eavAttribute->getIdByCode('catalog_product', 'value');
$proWeightAttId = $eavAttribute->getIdByCode('catalog_product', 'weight');
$catalogProductEntityVarchar = $connection->getTableName('catalog_product_entity_varchar');
$catalogProductEntityDecimal = $connection->getTableName('catalog_product_entity_decimal');
$cataloginventoryStockItem = $connection->getTableName('cataloginventory_stock_item');
$choose = $connection->choose()->from(['e' => $productTable], ['entity_id']);
if (!empty($ids)) {
$select->the place(
"e.entity_id IN(?)",
$ids
);
}
$sql = $catalogProductEntityVarchar.' as cpev';
$cond = 'e.entity_id = cpev.entity_id';
$fields = ['product_name' => 'value'];
$choose
->be part of($sql, $cond, $fields)
->the place('cpev.store_id = 0 AND cpev.attribute_id = '.$productAttributeId);
$sql = $catalogProductEntityDecimal.' as cped';
$cond = 'e.entity_id = cped.entity_id';
$fields = ['product_price' => 'value'];
$choose
->be part of($sql, $cond, $fields)
->the place('cped.store_id = 0 AND (cped.attribute_id =
'.$proPriceAttId.')');
$select->be part of(
$cataloginventoryStockItem.' as csi',
'e.entity_id = csi.product_id',
["product_qty" => "qty"]
)->the place("csi.website_id = 0 OR csi.website_id = 1");
$information = $connection->fetchAll($choose);
foreach ($information as $merchandise) {
$entityId = $merchandise['entity_id'];
$identify = $merchandise['product_name'];
$value = $merchandise['product_price'];
$amount = $merchandise['product_qty'];
//save information in your customized indexer desk
$connection->insertOnDuplicate($customTable, [
'product_id' => $entityId,
'name' => $name,
'price' => $price,
'qty' => $quantity
]);
}
}
public perform executeFull()
{
$this->execute([]);
}
public perform executeList(array $ids)
{
$this->execute($ids);
}
public perform executeRow($id)
{
$this->execute([$id]);
}
}
Step 6: Configure Dependency Injection To make sure that your customized indexer is correctly instantiated and used throughout the Magento system, it’s essential to configure dependency injection.
<kind identify="MagentoFrameworkIndexerConfigInterface">
<arguments>
<argument identify="indexers" xsi:kind="array">
<merchandise identify="product_attributes_indexer" xsi:kind="string">WebkulTestModelIndexerProductAttributesIndexer</merchandise>
</argument>
</arguments>
</kind>
Step7: Check and Confirm Upon getting carried out your customized indexer, it’s vital to completely check and confirm its performance. Run the required instructions to set off indexing and make sure that your information is correctly listed and searchable.
php bin/magento indexer:reindex product_attributes_indexer
Now let’s create the perform in api mannequin to make use of the above indexer outcome:
<?xml model="1.0"?>
<routes xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xsi:noNamespaceSchemaLocation="urn:magento:module:Magento_Webapi:and so forth/webapi.xsd">
<route url="/V1/check/api/index" methodology="GET">
<service class="WebkulTestApiTestApiManagementInterface" methodology="getIndexData"/>
<assets>
<useful resource ref="nameless"/>
</assets>
</route>
</routes>
public perform getIndexData()
{
/** WebkulTestModelResourceModelAttributeCollectionFactory */
$attributeCollection = $this->attributeCollectionFactory->create();
return $attributeCollection->getData();
}
As you possibly can test the results of each the method, it took half the time in fetching the outcome utilizing the customized indexers outcome.
We hope it would assist you to. Thanks!!
If any subject or doubt please be at liberty to say in remark part.
We’d be joyful to assist. Completely happy Coding!!
