Changeset 2794299

Timestamp:

10/04/2022 11:42:43 PM (3 years ago)

Author:

pau1andrews

Message:

improved product query

Location:

profitori/trunk

Files:

• profitori.php (modified) (7 diffs)
• readme.txt (modified) (1 diff)

profitori/trunk/profitori.php

@@ -7 +7 @@
 Plugin URI: https://profitori.com/
 Description: Profitori: WooCommerce ERP Plugin - Purchase Orders, Stock Management and Profit Margin Tracking
-Version: 2.1.0.2
+Version: 2.1.0.3
 Author: Unity Business Technology Pty Ltd
 Text Domain: profitori
@@ -622 +622 @@
     </style>
   <?php
+  wp_deregister_script('heartbeat');
   echo "<div id='stocktend-root' data-defaultcaption='$caption'><div class=\"lds-ring\"><div></div><div></div><div></div><div></div></div></div>";
 }
@@ -5560 +5561 @@
   function field_names_to_product_meta_joins($aNames) {
     global $wpdb;
+    global $prfi_subselects;
     $res = '';
+    if ( $prfi_subselects )
+      return $res;
     $idx = 0;
     foreach ( $aNames as $name ) {
@@ -5591 +5595 @@
   function get_polylang_clauses($options) {
     global $wpdb;
+    global $prfi_subselects;
     $res = array("cols" => "", "joins" => "");
     $ignore_polylang = $options && isset($options["ignore_polylang"]) ? true : false;
@@ -5599 +5604 @@
       return $res;
     $db = $wpdb->prefix;
-    $res["cols"] = ", tt.description polylang_language ";
-    $res["joins"] = "
-      INNER JOIN {$db}term_relationships tr ON p.ID = tr.object_id
-      INNER JOIN {$db}term_taxonomy tt ON tr.term_taxonomy_id = tt.term_taxonomy_id and tt.taxonomy = 'language'
-    ";
+/*
+    if ( $prfi_subselects ) {
+      $res["cols"] = ", 
+        (
+          SELECT tt.description 
+          FROM {$db}term_relationships tr 
+          INNER JOIN {$db}term_taxonomy tt 
+            ON tr.term_taxonomy_id = tt.term_taxonomy_id and tt.taxonomy = 'language' 
+          WHERE 
+            p.ID = tr.object_id
+          LIMIT 1
+        ) polylang_language ";
+      $res["joins"] = "";
+    } else {
+*/
+      $res["cols"] = ", tt.description polylang_language ";
+      $res["joins"] = "
+        INNER JOIN {$db}term_relationships tr ON p.ID = tr.object_id
+        INNER JOIN {$db}term_taxonomy tt ON tr.term_taxonomy_id = tt.term_taxonomy_id and tt.taxonomy = 'language'
+      ";
+/*
+    }
+*/
     return $res;
   }
@@ -5609 +5632 @@
   function get_prod_rows($options) {
     global $wpdb;
+    global $prfi_subselects;
+    $prfi_subselects = prfi_conf_val('subselects') === 'Yes';
     $polylang_clauses = $this->get_polylang_clauses($options);
     $polylang_cols = $polylang_clauses["cols"];
     $polylang_joins = $polylang_clauses["joins"];
     $fieldNames = $this->get_product_metakey_fields();
-    $cols = $this->field_names_to_cols($fieldNames); // Note: secured within field_names_to_cols
+    $cols = $this->field_names_to_cols($fieldNames, true); // Note: secured within field_names_to_cols
     $joins = $this->field_names_to_product_meta_joins($fieldNames); // Note: secured within field_names_to_product_meta_joins
     $db = $wpdb->prefix;
@@ -5622 +5647 @@
     else
       $modifiedAfter = '1970-01-01 00:00:01';
-    $sql = $wpdb->prepare("
-        SELECT  p.ID,
-                p.post_title,
-                p.post_parent,
-                p.post_modified,
-                CONVERT(pm.meta_value, SIGNED INTEGER) _stock,
-                sku.meta_value _sku,
-                COALESCE(low_stock_amount.meta_value, parent_low_stock_amount.meta_value) _low_stock_amount,
-                regular_price.meta_value _regular_price,
-                IF(tax_class.meta_value <> 'parent', tax_class.meta_value, parent_tax_class.meta_value) _tax_class,
-                tax_status.meta_value _tax_status,
-                pa.meta_value _product_attributes,
-                IF ( p.post_status IN ('publish', 'private'), '', 'DELETE' ) __incrementalDelete
-                $cols
-                $polylang_cols
-        FROM    {$db}posts p
-                LEFT JOIN {$db}postmeta pm ON (p.ID = pm.post_id AND pm.meta_key = '_stock')
-                LEFT JOIN {$db}postmeta sku ON (p.ID = sku.post_id AND sku.meta_key = '_sku')
-                LEFT JOIN {$db}postmeta tax_status ON 
-                  (p.ID = tax_status.post_id AND tax_status.meta_key = '_tax_status')
-                LEFT JOIN {$db}postmeta tax_class ON 
-                  (p.ID = tax_class.post_id AND tax_class.meta_key = '_tax_class')
-                LEFT JOIN {$db}postmeta low_stock_amount ON 
-                  (p.ID = low_stock_amount.post_id AND low_stock_amount.meta_key = '_low_stock_amount')
-                LEFT JOIN {$db}postmeta regular_price ON 
-                  (p.ID = regular_price.post_id AND regular_price.meta_key = '_regular_price')
-                LEFT JOIN {$db}postmeta parent_low_stock_amount ON 
-                  (p.post_parent = parent_low_stock_amount.post_id AND parent_low_stock_amount.meta_key = '_low_stock_amount')
-                LEFT JOIN {$db}postmeta parent_tax_class ON 
-                  (p.post_parent = parent_tax_class.post_id AND parent_tax_class.meta_key = '_tax_class')
-                INNER JOIN {$db}postmeta manageStock ON (p.ID = manageStock.post_id AND manageStock.meta_key = '_manage_stock')
-                LEFT JOIN {$db}postmeta pa ON (p.ID = pa.post_id AND pa.meta_key = '_product_attributes')
-                $joins
-                $polylang_joins
-        WHERE   p.post_type IN ('product', 'product_variation')
-                AND p.post_modified >= %s
-                AND ((%s = 'includeDeleted') OR (p.post_status IN ('publish', 'private')))
-                AND manageStock.meta_value <> 'no'
-        " . $this->get_subset_condition() . "
-        GROUP BY p.ID
-        ORDER BY p.ID ASC;
-        ",
-      $modifiedAfter,
-      $includeDeleted
-    );
+    if ( $prfi_subselects ) {
+      $sql = $wpdb->prepare("
+          SELECT  p.ID,
+                  p.post_title,
+                  p.post_parent,
+                  p.post_modified,
+                  CONVERT(
+                    (SELECT pm.meta_value FROM {$db}postmeta pm WHERE (p.ID = pm.post_id AND pm.meta_key = '_stock') LIMIT 1), 
+                    SIGNED INTEGER) _stock,
+                  (SELECT sku.meta_value FROM {$db}postmeta sku WHERE (p.ID = sku.post_id AND sku.meta_key = '_sku') LIMIT 1) _sku,
+                  COALESCE(
+                    (SELECT low_stock_amount.meta_value FROM {$db}postmeta low_stock_amount 
+                      WHERE (p.ID = low_stock_amount.post_id AND low_stock_amount.meta_key = '_low_stock_amount') 
+                      LIMIT 1), 
+                    (SELECT parent_low_stock_amount.meta_value FROM {$db}postmeta parent_low_stock_amount
+                      WHERE (p.post_parent = parent_low_stock_amount.post_id AND parent_low_stock_amount.meta_key = '_low_stock_amount')
+                      LIMIT 1)
+                  ) 
+                    _low_stock_amount,
+                  (SELECT regular_price.meta_value FROM {$db}postmeta regular_price
+                    WHERE (p.ID = regular_price.post_id AND regular_price.meta_key = '_regular_price') LIMIT 1) _regular_price,
+                  IF(
+                    (SELECT tax_class.meta_value FROM {$db}postmeta tax_class 
+                      WHERE (p.ID = tax_class.post_id AND tax_class.meta_key = '_tax_class') LIMIT 1)
+                    <> 'parent', 
+                    (SELECT tax_class.meta_value FROM {$db}postmeta tax_class 
+                      WHERE (p.ID = tax_class.post_id AND tax_class.meta_key = '_tax_class') LIMIT 1), 
+                    (SELECT parent_tax_class.meta_value FROM {$db}postmeta parent_tax_class 
+                      WHERE (p.post_parent = parent_tax_class.post_id AND parent_tax_class.meta_key = '_tax_class') LIMIT 1)
+                  ) 
+                    _tax_class,
+                  (SELECT tax_status.meta_value FROM {$db}postmeta tax_status 
+                    WHERE (p.ID = tax_status.post_id AND tax_status.meta_key = '_tax_status') LIMIT 1) _tax_status,
+                  (SELECT pa.meta_value FROM {$db}postmeta pa 
+                    WHERE (p.ID = pa.post_id AND pa.meta_key = '_product_attributes') LIMIT 1) _product_attributes,
+                  IF ( p.post_status IN ('publish', 'private'), '', 'DELETE' ) __incrementalDelete
+                  $cols
+                  $polylang_cols
+          FROM    {$db}posts p
+                  $polylang_joins
+          WHERE   p.post_type IN ('product', 'product_variation')
+                  AND p.post_modified >= %s
+                  AND ((%s = 'includeDeleted') OR (p.post_status IN ('publish', 'private')))
+                  AND 
+                    (SELECT manageStock.meta_value FROM {$db}postmeta manageStock 
+                      WHERE (p.ID = manageStock.post_id AND manageStock.meta_key = '_manage_stock') LIMIT 1)
+                    <> 'no'
+          " . $this->get_subset_condition() . "
+          GROUP BY p.ID
+          ORDER BY p.ID ASC;
+          ",
+        $modifiedAfter,
+        $includeDeleted
+      );
+    } else {
+      $sql = $wpdb->prepare("
+          SELECT  p.ID,
+                  p.post_title,
+                  p.post_parent,
+                  p.post_modified,
+                  CONVERT(pm.meta_value, SIGNED INTEGER) _stock,
+                  sku.meta_value _sku,
+                  COALESCE(low_stock_amount.meta_value, parent_low_stock_amount.meta_value) _low_stock_amount,
+                  regular_price.meta_value _regular_price,
+                  IF(tax_class.meta_value <> 'parent', tax_class.meta_value, parent_tax_class.meta_value) _tax_class,
+                  tax_status.meta_value _tax_status,
+                  pa.meta_value _product_attributes,
+                  IF ( p.post_status IN ('publish', 'private'), '', 'DELETE' ) __incrementalDelete
+                  $cols
+                  $polylang_cols
+          FROM    {$db}posts p
+                  LEFT JOIN {$db}postmeta pm ON (p.ID = pm.post_id AND pm.meta_key = '_stock')
+                  LEFT JOIN {$db}postmeta sku ON (p.ID = sku.post_id AND sku.meta_key = '_sku')
+                  LEFT JOIN {$db}postmeta tax_status ON 
+                    (p.ID = tax_status.post_id AND tax_status.meta_key = '_tax_status')
+                  LEFT JOIN {$db}postmeta tax_class ON 
+                    (p.ID = tax_class.post_id AND tax_class.meta_key = '_tax_class')
+                  LEFT JOIN {$db}postmeta low_stock_amount ON 
+                    (p.ID = low_stock_amount.post_id AND low_stock_amount.meta_key = '_low_stock_amount')
+                  LEFT JOIN {$db}postmeta regular_price ON 
+                    (p.ID = regular_price.post_id AND regular_price.meta_key = '_regular_price')
+                  LEFT JOIN {$db}postmeta parent_low_stock_amount ON 
+                    (p.post_parent = parent_low_stock_amount.post_id AND parent_low_stock_amount.meta_key = '_low_stock_amount')
+                  LEFT JOIN {$db}postmeta parent_tax_class ON 
+                    (p.post_parent = parent_tax_class.post_id AND parent_tax_class.meta_key = '_tax_class')
+                  INNER JOIN {$db}postmeta manageStock ON (p.ID = manageStock.post_id AND manageStock.meta_key = '_manage_stock')
+                  LEFT JOIN {$db}postmeta pa ON (p.ID = pa.post_id AND pa.meta_key = '_product_attributes')
+                  $joins
+                  $polylang_joins
+          WHERE   p.post_type IN ('product', 'product_variation')
+                  AND p.post_modified >= %s
+                  AND ((%s = 'includeDeleted') OR (p.post_status IN ('publish', 'private')))
+                  AND manageStock.meta_value <> 'no'
+          " . $this->get_subset_condition() . "
+          GROUP BY p.ID
+          ORDER BY p.ID ASC;
+          ",
+        $modifiedAfter,
+        $includeDeleted
+      );
+    }
     $res = $wpdb->get_results($sql);
     if ( $polylang_cols ) {

profitori/trunk/readme.txt

@@ -367 +367 @@
 
 == Changelog ==
+
+= 2.1.0.2 =
+* Tuned performance of product query
 
 = 2.1.0.1 =