php - 在opencart中将商店id添加到产品导出中(mysql查询)

Question

另一个奇怪的问题需要一些解释，提前感谢您的帮助。

我继承了一个 opencart 安装，并被要求修改一个报告生成器(Product Export Express)，该生成器运行一个大型 MySQL 查询，以吐出所有相关产品信息以及其他数据的 xls 文件。现在我正在使用以下代码:

    <?php
ini_set("memory_limit","1G");   
class ModelInventoryExpress extends Model
{


    public function getProducts(){
        $products_sql = "SELECT
        p.product_id AS             `Product ID`, 
        p.model AS                  `Model`,
        p.sku as                    `SKU`,
        pd.name AS                  `Product Name`,
        cd.name as                  `Category`,
        p.location AS               `Location`,
        p.quantity AS               `Quanity`,
        (SELECT `text` 
        FROM product_attribute pa 
        WHERE pa.product_id = p.product_id 
        AND pa.attribute_id = 6) AS `Box Count`,

        (SELECT `text` 
        FROM product_attribute pa 
        WHERE pa.product_id = p.product_id 
        AND pa.attribute_id = 7) AS `Length`,

        (SELECT `text` 
        FROM product_attribute pa 
        WHERE pa.product_id = p.product_id 
        AND pa.attribute_id = 5) AS `Ring Gauge`,

        (SELECT `text` 
        FROM product_attribute pa 
        WHERE pa.product_id = p.product_id 
        AND pa.attribute_id = 4) AS `Strength`,

        (SELECT `text` 
        FROM product_attribute pa 
        WHERE pa.product_id = p.product_id 
        AND pa.attribute_id = 3) AS `Wrapper`,

        CASE
        WHEN p.`status` = 1
        THEN 'active'
        ELSE 'non-active'
        END  AS `Status` ,
        md.name as                  `Manufacturer`,
        FORMAT(p.price, 2) as   `Price`,
        FORMAT(p.cost, 2) as    `Cost`,
        FORMAT(p.wholesale, 2) as   `Wholesale`,                

        CASE
        WHEN pts.`store_id` = 0
        THEN 'StogieBoys.com'
        WHEN pts.`store_id` = 1
        THEN 'CigarHeist.com'
        WHEN pts.`store_id` = 2
        THEN 'm.stogieboys.com'
        WHEN pts.`store_id` = 3
        THEN 'BestCigarStuff.com'
        WHEN pts.`store_id` = 6
        THEN 'SBCigarWholesale.com'
        WHEN pts.`store_id` = 8
        THEN 'StogieTrade.com'
        ELSE 'None'
        END  AS                         `Store`,

        CASE
        WHEN p.`is_dropshipped` = 1
        THEN 'Yes'
        ELSE 'No'
        END  AS                         `Is Dropshipped`        

        FROM
        product p,
        product_description pd,
        product_to_category ptc,
        category_description cd,
        manufacturer md,
        product_to_store pts
        WHERE 1
        AND p.product_id = pd.product_id
        AND p.product_id = ptc.product_id
        AND ptc.category_id = cd.category_id
        AND p.manufacturer_id = md.manufacturer_id
        AND p.product_id = pts.product_id
        ORDER BY pd.name        
         "; 
         $query = $this->db->query($products_sql);
         return $query->rows;   
    }
}

在大多数情况下，这工作得很好。失败的地方是 store_id 部分。 CASE 将仅返回第一个匹配的值，然后继续下一位。我需要它来匹配并列出产品可能出现的所有商店，就像类别一样。

当上面的方法不起作用时，我尝试将其设置为 AS“类别”，如下所示:

s.name as                   `Store`,

CASE
WHEN p.`is_dropshipped` = 1
THEN 'Yes'
ELSE 'No'
END  AS                         `Is Dropshipped`        

FROM
product p,
product_description pd,
product_to_category ptc,
category_description cd,
manufacturer md,
store s,
product_to_store pts
WHERE 1
AND p.product_id = pd.product_id
AND p.product_id = ptc.product_id
AND ptc.category_id = cd.category_id
AND p.manufacturer_id = md.manufacturer_id
AND p.product_id = pts.product_id
AND pts.store_id = s.store_id
ORDER BY pd.name    

但这并没有按预期工作，因为在商店表中，默认商店为 0，并且该表以多商店设置的其他商店开始。 IE。 1 等等，但不列出默认值，或 0 商店。

设计此查询以获得我希望的结果的最佳方法是什么？

Answer 1

哦，伙计，你听说过JOIN(LEFT，RIGHT，OUTER, ...)?

您的巨大查询是如何做错误的一个非常好的示例，下面是应该如何正确的方式完成此操作的示例:

public function getProducts() {
    // retrieve products with basic data
    $products = $this->db->query("SELECT
    p.product_id              `Product ID`, 
    p.model                   `Model`,
    p.sku                     `SKU`,
    pd.name                   `Product Name`,
    cd.name                   `Category`,
    p.location                `Location`,
    p.quantity                `Quanity`,
    p.`status`                `Status` ,
    md.name                   `Manufacturer`,
    FORMAT(p.price, 2)        `Price`,
    FORMAT(p.cost, 2)         `Cost`,
    FORMAT(p.wholesale, 2)    `Wholesale`,                
    pts.`store_id`            `Store`,
    p.`is_dropshipped`        `Is Dropshipped`        

    FROM product p,
        LEFT JOIN product_description pd ON pd.product_id = p.product_id AND pd.language_id = " . (int)$this->config->get('config_language_id') . "
        LEFT JOIN product_to_category ptc ON ptc.product_id = p.product_id,
        LEFT JOIN category_description cd ON cd.category_id = ptc.category_id AND cd.language_id = " . (int)$this->config->get('config_language_id') . "
        LEFT JOIN manufacturer m ON m.manufacturer_id = p.manufacturer_id
        LEFT JOIN product_to_store pts ON pts.product_id = p.product_id

    ORDER BY pd.name")->rows;

    foreach($products as $key => $product) {
        $products[$key]['Box Count']  = $this->getProductAttributeValue($product['product_id'], 6);
        $products[$key]['Length']     = $this->getProductAttributeValue($product['product_id'], 7);
        $products[$key]['Ring Gauge'] = $this->getProductAttributeValue($product['product_id'], 5);
        $products[$key]['Strength']   = $this->getProductAttributeValue($product['product_id'], 4);
        $products[$key]['Wrapper']    = $this->getProductAttributeValue($product['product_id'], 3);
    }

    return $products;   
}

public function getProductAttributeValue($product_id, $attribute_id) {
    $query = $this->db->query("SELECT `text` 
    FROM product_attribute pa 
    WHERE pa.product_id = " . (int).$product_id . "
    AND pa.attribute_id = " . (int)$attribute_id);

    return $query->row['text'];
}

正如你可能提到的，我从 SQL 中删除了所有 CASE WHEN，因为这些 status、store_id 等的逻辑解释应该完成在您的模板或 Controller 中。 SQL 查询(和模型)应该只检索和存储数据，并且您应该使您的 SQL 查询尽可能简单...还要提及 LEFT JOIN 的使用是...

对于 0 商店(您的产品中将是 NULL 或 none 值)，当然会显示正确的商店名称。它不是 0(零)，而是 NULL，而是一个简单的检查:

if($product['store_id']) {
    // we have store_id
} else {
    // store_id is not set (NULL), display as the main store
}

这里就足够了...