将这个带有 HABTM 相关数据的庞大查询转换为 CakePHP 查找

Question

已经很晚了，我写了这个巨大的查询来根据我已经找到的产品获取相关产品。

我需要获取同一类别 (HABTM) 中的产品、父产品、具有相同父项的产品（兄弟/邻居）以及作为当前产品的直接子项的产品（只有一层嵌套）。我有当前产品的产品 ID 及其parent_id。如果可以在产品上设置 Product.published = 1 的条件，那就太好了，但如果它会使查询变得如此之大，我可以随时检查。此外，我需要排除当前产品。

SELECT `products`.*
FROM `products`, `categories_products`
WHERE
(
    (
        `categories_products`.`product_id` = `products`.`id`
        AND `categories_products`.`category_id` IN (
            SELECT `category_id`
            FROM `categories_products`
            WHERE `categories_products`.`product_id` = '$product_id'
        )
    )
    OR `products`.`parent_id` = '$parent_id'
    OR `products`.`parent_id` = '$product_id'
    OR `products`.`id` = '$parent_id'
)
AND `product`.`id` <> '$product_id'

GROUP BY `products`.`id`

甚至可以进一步优化它，到目前为止我已经：

public function related($productData, $limit = 4) {

    $conditions = array(
        'OR' => array(array('Product.parent_id' => $productData['Product']['id'])), // Children of product),
        'Product.id <>' => $productData['Product']['id']
    );

    if(!empty($product['parent_id'])) {
        $conditions['OR'][] = array('Product.parent_id' => $productData['Product']['parent_id']); // Siblings
        $conditions['OR'][] = array('Product.id' => $productData['Product']['parent_id']); // Parent of product
    }

    return $this->find('all', array(
        'conditions' => $conditions,
        'contain' => array('Category'),
        'group' => 'Product.id',
        'limit' => $limit
    ));
}

Answer 1

您将需要使用cake的复杂查找条件语法（向下滚动到子查询部分）。

Answer 2

老实说，我不认为这是从 mysql 获取“相关”产品的最佳方法，这就是搜索引擎的用途。尤其是在处理大数据时，您的类似类别方法注定会失败。

说了这么多之后，这是对当前 sql 的重写，希望能让您获得一些性能。

SELECT * FROM products p
WHERE 
    p.published = 1 AND 
    p.id != $product_id AND
    (
        p.id IN 
            (
                SELECT DISTINCT(cp2.product_id) FROM categories_product cp1
                LEFT JOIN categories_product cp2 ON cp1.category_id = cp2.category_id
                WHERE cp1.product_id = $product_id
                UNION SELECT $parent_id
            )
        OR p.parent_id IN($parent_id, $product_id)
    )
;

我试图摆脱不必要的 group by 语句。希望这有帮助。

PS：由于我是在文本编辑器中编写的，因此可能存在语法错误。

Answer 3

我会阻止在类别上递归尝试 SELECT IN 的需要。根据相关产品进行预构建并获取其所有不同类别。由此，获得与类别相匹配的独特产品。现在，您有一个“CommonByCategory”的预查询，它将已经是 ID 的单个实例。

接下来，根据您尝试获得资格的特定 ID 再次对产品“OriginalProduct”进行硬连接。因为它总是存在并且永远不会改变，所以我们可以使用它作为兄弟姐妹进行比较的指针，也可以用于父 ID 匹配（如果不为空——通过应用 IFNULL() 测试），

因为每个产品只会扫描一次并且由于存在多个类别的可能性而不会返回多个条目，因此不需要“GROUP BY”。

SELECT STRAIGHT_JOIN 
      p.*
   from 
      products p
         left join 
            ( SELECT DISTINCT 
                    cp2.product_id
                 from
                    ( SELECT cp.Category_ID
                         from categories_products cp
                         where cp.product_id = '$product_id' ) JustCats
                     join categories_products cp2
                        ON JustCats.Category_ID = cp2.Category_ID ) as CommonByCategory
            ON p.ID = CommonByCategory.product_ID

         join products OriginalProduct
            ON OriginalProduct.ID = '$product_id'

   where
          p.id <> '$product_id'
      and ( IFNULL( CommonByCategory.Product_ID, -1) > 0
          OR p.id = IFNULL( OriginalProduct.Parent_ID, -1 )
          OR p.parent_id = OriginalProduct.id

Answer 4

这个问题又出现了，我百分百解决了！这是我完成的代码：

// Model
public function related($product, $limit = 9) {

    // Children of product
    $conditions = array(
        'OR' => array(array('Product.parent_id' => $product['Product']['id'])), // Children of product),
        'Product.id <>' => $product['Product']['id'],
        'Product.published' => 1
    );

    // Siblings and parent of product if applicable
    if (!empty($product['Product']['parent_id'])) {

        $conditions['OR'][] = array('Product.parent_id' => $product['Product']['parent_id']);
        $conditions['OR'][] = array('Product.id' => $product['Product']['parent_id']);
    }

    // Products in the same categories
    // Get category IDs in an array
    $categoryIds = Set::extract($product['Category'], '{n}.id');

    $conditionsSubQuery['category_id IN(?)'] = implode(',', $categoryIds);

    $db = $this->getDataSource();
    $subQuery = $db->buildStatement(
            array(
        'fields' => array('product_id'),
        'table' => 'categories_products',
        'joins' => array(),
        'alias' => 'c_p',
        'conditions' => $conditionsSubQuery,
        'order' => null,
        'group' => null,
        'limit' => null
            ), $this->CategoryProduct
    );
    $subQuery = 'Product.id IN (' . $subQuery . ') ';
    $subQueryExpression = $db->expression($subQuery);

    $conditions['OR'][] = $subQueryExpression;

    return $this->find('all', array(
                'conditions' => $conditions,
                'contain' => array('Category'),
                'group' => 'Product.id',
                'limit' => $limit
            ));