Magento2 为多个网站批量修改 meta title

Posted by

批量修改网站产品的 meta title

批量修改网站产品的 meta title

我有一个magento2 网站站点,在这个网站上有一个 mg1.yshuq.com 网站,这个网站上有一批产品。

mg1.yshuq.com 站上,有一个产品属性 meta tile 的值为:产品名称–mg1.yshuq.com

我现在又创建另外一个站点mg2.yshuq.com,我将mg1.yshuq.com 网站上的产品追加到了mg2.yshuq.com 站上。但是我的产品属性 meta tile 的值仍为:产品名称–mg1.yshuq.com,我想把

meta title的值修改为 产品名称–mg2.yshuq.com 该怎么操作。

1.我们找到meta tile 产品属性存放表。

eav_attribute

magento2 属性表 eav_attribute

select * from eav_attribute where  attribute_code = 'meta_title'
meta_title

我们找到属性为meta_title 的attribute_id为84,那么我们现在根据这个id的值,去查询属性的值。

我们找到存放属性的值的表 catalog_product_entity_varchar。我们根据 attribute_id = 84 查询

select * from catalog_product_entity_varchar where  attribute_id = 84

根据上面sql语句我们查询结果如下:

catalog_product_entity_varchar

从查询结果我们找到了

  • value_id :主键自增
  • attribute_id: meta tile的id
  • store_id : 店铺id
  • entity_id: 产品id
  • value: meta title的值

2.获取所有网站 store_website 循环遍历网站

$table = $connection->getTableName('store_website');
        $query = "Select * FROM " . $table . " where `name` != 'Admin'";
        $logger->info("查询所有网站:".$query);
        $websiteList = $connection->fetchAll($query);
        $logger->info("所有网站list:".\GuzzleHttp\json_encode($websiteList));

        if (!empty($websiteList) && count($websiteList) > 0) {
            $webId = $websiteName = $query = '';
            foreach ($websiteList as $v1 => $j1) {
                $websiteName = $j1['name'];
                $webId = $j1['website_id'];
             }
         }
mysql> select * from store_website;
+------------+-------------+-----------------+------------+------------------+------------+
| website_id | code        | name            | sort_order | default_group_id | is_default |
+------------+-------------+-----------------+------------+------------------+------------+
|          0 | admin       | Admin           |          0 |                0 |          0 |
|          1 | base        | uikeyddealw      |          0 |                1 |          1 |
|          2 | dev         | dev             |          1 |                3 |          0 |
|          3 | dev_keeysfang | dev_keeysfang.com |          1 |                2 |          0 |
+------------+-------------+-----------------+------------+------------------+------------+
4 rows in set (0.00 sec)

3. 根据website_id 循环遍历产品

  //3.根据website_id 循环遍历产品      product_id
                $table = $connection->getTableName('catalog_product_website');
                $query = "Select * FROM " . $table . " where website_id = " . $webId;
                $productList = $connection->fetchAll($query);
                $logger->info("根据网站id查询所有产品:".$query);
                $logger->info("网站id:".$webId."的全部产品".\GuzzleHttp\json_encode($productList));

                if (!empty($productList) && count($productList) > 0) {
                    foreach ($productList as $v2 => $j2) {
                        $productId = $j2['product_id'];
                        $logger->info("product id:" . $productId);
             }
     }
mysql> select * from store_website;
+------------+-------------+-----------------+------------+------------------+------------+
| website_id | code        | name            | sort_order | default_group_id | is_default |
+------------+-------------+-----------------+------------+------------------+------------+
|          0 | admin       | Admin           |          0 |                0 |          0 |
|          1 | base        | ukeydeal        |          0 |                1 |          1 |
|          2 | dev         | dev             |          1 |                3 |          0 |
|          3 | dev_keysfan | dev_keeysfang.com |          1 |                2 |          0 |
+------------+-------------+-----------------+------------+------------------+------------+
4 rows in set (0.00 sec)

4.根据website_id 在store表查询出店铺id

//4.根据website_id 在store表查询出店铺id
                        $table = $connection->getTableName('store');
                        $query = "Select * FROM " . $table . " where `website_id` =" . $webId;
                        $storeList = $connection->fetchAll($query);
                        $logger->info("根据website_id查询店铺列表" . $query);
                        $logger->info("网站id: ".$webId."店铺列表".\GuzzleHttp\json_encode($storeList));
                        if (!empty($storeList) && count($storeList) > 0) {
                            foreach ($storeList as $v3 => $j3) {
                                $storeId = $j3['store_id'];
                              }
} 
mysql> select * from store where website_id = 1;
+----------+---------------+------------+----------+--------------+------------+-----------+
| store_id | code          | website_id | group_id | name         | sort_order | is_active |
+----------+---------------+------------+----------+--------------+------------+-----------+
|        1 | ukd_en        |          1 |        1 | English      |          0 |         1 |
|        2 | ukd_tw        |          1 |        1 | 繁体中文     |          0 |         1 |
|        5 | dev_ukeydal   |          1 |        1 | 繁体中文     |          0 |         1 |
|        6 | developdealom |          1 |        1 | 繁体中文     |          0 |         1 |
+----------+---------------+------------+----------+--------------+------------+-----------+
4 rows in set (0.00 sec)

5. 获取 默认店铺admin store_id = 0 的value的值

 $table = $connection->getTableName('catalog_product_entity_varchar');
                                $query = "Select * FROM " . $table . " where store_id = 0 and entity_id = " . $productId
                                    . " and  attribute_id = " . $attributeId;
                                $metaTitleValue0 = $connection->fetchAll($query);
                                $logger->info("获取默认值sql:".$query);
                                $logger->info("获取admin点meta title默认值:".\GuzzleHttp\json_encode($metaTitleValue0));
elect * from catalog_product_entity_varchar where store_id=0  and attribute_id=84 and entity_id=2;
+----------+--------------+----------+-----------+----------------------------------------------+
| value_id | attribute_id | store_id | entity_id | value                                        |
+----------+--------------+----------+-----------+----------------------------------------------+
|       21 |           84 |        0 |         2 | Buy Anno 1800, Anno Uplay Key - ukeydeal.com |
+----------+--------------+----------+-----------+----------------------------------------------+
1 row in set (0.00 sec)

6. 根据店铺id,产品id ,属性id,查询 属性为meta_tile的值

  $query = "Select * FROM " . $table . " where store_id = " . $storeId . " and entity_id = " . $productId
                                    . " and  attribute_id = " . $attributeId;
                                $metaTitleValue1  = $connection->fetchAll($query);
                                $logger->info("获取当前网站店铺产品的meta title sql:".$query);
                                $logger->info("获取当前网站店铺产品的meta title 的值:".\GuzzleHttp\json_encode($metaTitleValue1));
                            

mysql> select * from catalog_product_entity_varchar where store_id=1   and attribute_id=84 and entity_id=2;
+----------+--------------+----------+-----------+-----------------------------------------+
| value_id | attribute_id | store_id | entity_id | value                                   |
+----------+--------------+----------+-----------+-----------------------------------------+
|     9079 |           84 |        1 |         2 | Buy Anno 1800, Anno Uplay Key -ukeydeal |
+----------+--------------+----------+-----------+-----------------------------------------+
1 row in set (0.00 sec)

7.组合新值,获取store id = 0 后台默认值

 //7.组合新值,获取store id = 0  后台默认值。
   $mt = explode('-', $metaTitleValue0[0]['value']);
   $newMetaTitleValue = $mt[0] . "-" . $websiteName;

8.meta title 不存在 就添加, 存在,就更新

if (empty($metaTitleValue1) && count($metaTitleValue1) == 0) {
                                    $data = [
                                        'attribute_id' => $attributeId,
                                        'store_id'     => $storeId,
                                        'entity_id'    => $productId,
                                        'value'        => $newMetaTitleValue
                                    ];
                                    $logger->info("添加的meta title 的值".\GuzzleHttp\json_encode($data));
                                    $connection->insert($table,$data);
                                }else{
                                    $id = $metaTitleValue1[0]['value_id'];
                                    $newMetaTitleValue = str_replace("'", ' ', $newMetaTitleValue);
                                    $sql = "UPDATE ".$table." SET `value` = '". $newMetaTitleValue."' WHERE  value_id = ".$id;
                                    $logger->info("更新meta title 的值".$sql);
                                    $connection->query($sql);
                                }

完整代码如下:

<?php
/**
 * Created by PhpStorm.
 * User: yshuq
 * Date: 2022/7/18
 * Time 2:57 PM
 */

declare(strict_types=1);

namespace Cg\MetaTitleCron\Cron;

use Magento\Framework\App\ResourceConnection;

class CronJob
{
    protected $resourceConnection;

    public function __construct(
        ResourceConnection $resourceConnection
    )
    {
        $this->resourceConnection = $resourceConnection;
    }

    public function execute()
    {
        //操作日志更改记录
        $writer = new \Zend\Log\Writer\Stream(BP . '/var/log/meta_title.log');
        $logger = new \Zend\Log\Logger();
        $logger->addWriter($writer);

        $connection = $this->resourceConnection->getConnection();
        //1.查询meta_title 的属性id
        $attributeId = '1';
        $table = $connection->getTableName('eav_attribute');
        $query = "Select * from " . $table . " WHERE `attribute_code` = 'meta_title'  and  `frontend_label` = 'Meta Title'";
        $logger->info("查询meta title 的 id:".$query);
        $result1 = $connection->fetchAll($query);
        if (!empty($result1) && count($result1) == 1) {
            $attributeId = $result1[0]['attribute_id'];
        }

        //2.获取所有网站 store_website 循环遍历网站        website_id
        $table = $connection->getTableName('store_website');
        $query = "Select * FROM " . $table . " where `name` != 'Admin'";
        $logger->info("查询所有网站:".$query);
        $websiteList = $connection->fetchAll($query);
        $logger->info("所有网站list:".\GuzzleHttp\json_encode($websiteList));

        if (!empty($websiteList) && count($websiteList) > 0) {
            $webId = $websiteName = $query = '';
            foreach ($websiteList as $v1 => $j1) {
                $websiteName = $j1['name'];
                $webId = $j1['website_id'];

                //3.根据website_id 循环遍历产品      product_id
                $table = $connection->getTableName('catalog_product_website');
                $query = "Select * FROM " . $table . " where website_id = " . $webId;
                $productList = $connection->fetchAll($query);
                $logger->info("根据网站id查询所有产品:".$query);
                $logger->info("网站id:".$webId."的全部产品".\GuzzleHttp\json_encode($productList));

                if (!empty($productList) && count($productList) > 0) {
                    foreach ($productList as $v2 => $j2) {
                        $productId = $j2['product_id'];
                        $logger->info("product id:" . $productId);
                        //4.根据website_id 在store表查询出店铺id
                        $table = $connection->getTableName('store');
                        $query = "Select * FROM " . $table . " where `website_id` =" . $webId;
                        $storeList = $connection->fetchAll($query);
                        $logger->info("根据website_id查询店铺列表" . $query);
                        $logger->info("网站id: ".$webId."店铺列表".\GuzzleHttp\json_encode($storeList));
                        if (!empty($storeList) && count($storeList) > 0) {
                            foreach ($storeList as $v3 => $j3) {
                                $storeId = $j3['store_id'];

                                //5.获取 默认店铺admin store_id = 0 的value的值
                                $table = $connection->getTableName('catalog_product_entity_varchar');
                                $query = "Select * FROM " . $table . " where store_id = 0 and entity_id = " . $productId
                                    . " and  attribute_id = " . $attributeId;
                                $metaTitleValue0 = $connection->fetchAll($query);
                                $logger->info("获取默认值sql:".$query);
                                $logger->info("获取admin点meta title默认值:".\GuzzleHttp\json_encode($metaTitleValue0));

                                //6.根据店铺id,产品id ,属性id,查询 属性为meta_tile的值,
                                $query = "Select * FROM " . $table . " where store_id = " . $storeId . " and entity_id = " . $productId
                                    . " and  attribute_id = " . $attributeId;
                                $metaTitleValue1  = $connection->fetchAll($query);
                                $logger->info("获取当前网站店铺产品的meta title sql:".$query);
                                $logger->info("获取当前网站店铺产品的meta title 的值:".\GuzzleHttp\json_encode($metaTitleValue1));
                                //7.组合新值,获取store id = 0  后台默认值。
                                $mt = explode('-', $metaTitleValue0[0]['value']);
                                $newMetaTitleValue = $mt[0] . "-" . $websiteName;
                                //8.meta title 不存在 就添加,  存在,就更新
                                if (empty($metaTitleValue1) && count($metaTitleValue1) == 0) {
                                    $data = [
                                        'attribute_id' => $attributeId,
                                        'store_id'     => $storeId,
                                        'entity_id'    => $productId,
                                        'value'        => $newMetaTitleValue
                                    ];
                                    $logger->info("添加的meta title 的值".\GuzzleHttp\json_encode($data));
                                    $connection->insert($table,$data);
                                }else{
                                    $id = $metaTitleValue1[0]['value_id'];
                                    $newMetaTitleValue = str_replace("'", ' ', $newMetaTitleValue);
                                    $sql = "UPDATE ".$table." SET `value` = '". $newMetaTitleValue."' WHERE  value_id = ".$id;
                                    $logger->info("更新meta title 的值".$sql);
                                    $connection->query($sql);
                                }
                            }
                        }
                    }
                }
            }
        }
    }
}