0

I'm trying to write a plugin to get Commerce Stock Level field working with views, so I can get it to work correctly with Entity Browser(the operator runs on the last transaction instead of the absolute level of stock). I'm a SQL syntax error and can't seem to get the right SQL statement configured in the View Filter Plugin to generate the SQL statement I want.

For reference, this is the mysql statement that I got working in mysql cli that I want to implement in the view filter:

select p.variation_id, p.type, s.entity_id, s.entity_type from commerce_product_variation p JOIN commerce_stock_transaction s ON p.variation_id = s.entity_id GROUP BY p.variation_id, p.type, s.entity_id, s.entity_type HAVING SUM(s.qty) > 0;

src/Plugin/views/filter/CommerceStockViewFilter.php:

<?php

namespace Drupal\commerce_stock_view_filter\Plugin\views\filter;

use Drupal\views\Plugin\views\filter\NumericFilter;
use Drupal\Core\Form\FormStateInterface;
use Drupal\views\ViewExecutable;
use Drupal\views\Plugin\views\display\DisplayPluginBase;
use Drupal\views\Views;

/**
 * Filters commerce stock field computed value
 *
 * @ingroup views_filter_handlers
 *
 * @ViewsFilter("commerce_stock_view_filter")
 */
class CommerceStockViewFilter extends NumericFilter {

  public function query() {
    $this->ensureMyTable();
    $configuration = [
      'table' => 'commerce_product_variation',
      'field' => 'variation_id',
      'left_table' => 'commerce_stock_transaction',
      'left_field' => 'entity_id',
      'operator' => '='
    ];
    $join = Views::pluginManager('join')->createInstance('standard', $configuration);
    $this->query->addRelationship('commerce_product_variation', $join, 'commerce_stock_transaction');
    $this->query->ensureTable('commerce_stock_transaction', $relationship, $join);

    $this->query->addGroupBy('commerce_product_variation.variation_id');
    $this->query->addGroupBy('commerce_product_variation.type');
    $this->query->addGroupBy('commerce_stock_transaction.entity_id');
    $this->query->addGroupBy('commerce_stock_transaction.entity_type');
    $this->query->addHavingExpression($this->options['group'], "SUM(commerce_stock_transaction.qty) ${$this->operator} ${$this->value}");
  }
}

commerce_stock_view_filter.module:

<?php

/**
 * @file
 * Contains commerce_stock_view_filter.module.
 */

use Drupal\Core\Routing\RouteMatchInterface;

/**
 * Implements hook_help().
 */
function commerce_stock_view_filter_help($route_name, RouteMatchInterface $route_match) {
  switch ($route_name) {
    // Main module help for the commerce_stock_view_filter module.
    case 'help.page.commerce_stock_view_filter':
      $output = '';
      $output .= '<h3>' . t('About') . '</h3>';
      $output .= '<p>' . t('Commerce Stock View Filter Module') . '</p>';
      return $output;

    default:
  }
}

/**
 * Implements hook_theme().
 */
function commerce_stock_view_filter_theme() {
  return [
    'commerce_stock_view_filter' => [
      'render element' => 'children',
    ],
  ];
}

function commerce_stock_view_filter_views_data_alter(&$data) {
  $data['commerce_product_variation']['commerce_stock_view_filter'] = [
      'title' => t('Commerce Stock Field View Filter'),
      'filter' => [
          'title' => t('Commerce Stock Field View Filter'),
          'help' => 'correctly filters on stock level instead of last transaction',
          'field' => 'variation_id',
          'id' => 'commerce_stock_view_filter',
      ]
  ];
}

The sql statement that is being generated by the Filter Plugin:

SELECT MIN(commerce_product_field_data.product_id) AS product_id, MIN(commerce_product_variation_field_data_commerce_product__variations.variation_id) AS commerce_product_variation_field_data_commerce_product__vari FROM {commerce_product_field_data} commerce_product_field_data LEFT JOIN {commerce_product__variations} commerce_product__variations ON commerce_product_field_data.product_id = commerce_product__variations.entity_id AND commerce_product__variations.deleted = :views_join_condition_0 INNER JOIN {commerce_product_variation_field_data} commerce_product_variation_field_data_commerce_product__variations ON commerce_product__variations.variations_target_id = commerce_product_variation_field_data_commerce_product__variations.variation_id INNER JOIN {commerce_product_variation} commerce_product_variation_field_data_commerce_product__variations__commerce_product_variation ON commerce_product_variation_field_data_commerce_product__variations.variation_id = commerce_product_variation_field_data_commerce_product__variations__commerce_product_variation.variation_id LEFT JOIN {commerce_product_variation} commerce_product_variation ON .entity_id = commerce_product_variation.variation_id WHERE (commerce_product_field_data.status = :db_condition_placeholder_1) AND (commerce_product_field_data.type IN (:db_condition_placeholder_2, :db_condition_placeholder_3, :db_condition_placeholder_4, :db_condition_placeholder_5, :db_condition_placeholder_6, :db_condition_placeholder_7, :db_condition_placeholder_8)) AND (commerce_product_variation_field_data_commerce_product__variations__commerce_product_variation.uuid IS NOT NULL) AND (commerce_product_variation_field_data_commerce_product__variations.status = :db_condition_placeholder_9) GROUP BY commerce_product_variation.variation_id, commerce_product_variation.type, commerce_stock_transaction.entity_id, commerce_stock_transaction.entity_type HAVING (SUM(commerce_stock_transaction.qty) )1

The PHP error:

Uncaught PHP Exception Drupal\\Core\\Database\\DatabaseExceptionWrapper: "Exception in Menu[menu]: SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '= commerce_product_variation.variation_id\nLEFT JOIN commerce_product__field_bran' at line 4: SELECT commerce_product__field_brand.field_brand_value AS commerce_product__field_brand_field_brand_value, commerce_product_field_data.title AS commerce_product_field_data_title, commerce_product_field_data.product_id AS product_id, MIN(commerce_product_field_data.product_id) AS product_id_1, MIN(commerce_product_variation_field_data_commerce_product__variations.variation_id) AS commerce_product_variation_field_data_commerce_product__vari\nFROM\n{commerce_product_field_data} commerce_product_field_data\nLEFT JOIN {commerce_product__variations} commerce_product__variations ON commerce_product_field_data.product_id = commerce_product__variations.entity_id AND commerce_product__variations.deleted = :views_join_condition_0\nINNER JOIN {commerce_product_variation_field_data} commerce_product_variation_field_data_commerce_product__variations ON commerce_product__variations.variations_target_id = commerce_product_variation_field_data_commerce_product__variations.variation_id\nINNER JOIN {commerce_product_variation} commerce_product_variation_field_data_commerce_product__variations__commerce_product_variation ON commerce_product_variation_field_data_commerce_product__variations.variation_id = commerce_product_variation_field_data_commerce_product__variations__commerce_product_variation.variation_id\nLEFT JOIN {commerce_product_variation} commerce_product_variation ON .entity_id = commerce_product_variation.variation_id\nLEFT JOIN {commerce_product__field_brand} commerce_product__field_brand ON commerce_product_field_data.product_id = commerce_product__field_brand.entity_id AND commerce_product__field_brand.deleted = :views_join_condition_1 AND (commerce_product__field_brand.langcode = commerce_product_field_data.langcode OR commerce_product__field_brand.bundle = :views_join_condition_3)\nWHERE (commerce_product_field_data.status = :db_condition_placeholder_4) AND (commerce_product_field_data.type IN (:db_condition_placeholder_5, :db_condition_placeholder_6, :db_condition_placeholder_7, :db_condition_placeholder_8, :db_condition_placeholder_9, :db_condition_placeholder_10, :db_condition_placeholder_11)) AND (commerce_product_variation_field_data_commerce_product__variations__commerce_product_variation.uuid IS NOT NULL) AND (commerce_product_variation_field_data_commerce_product__variations.status = :db_condition_placeholder_12)\nGROUP BY commerce_product_variation.variation_id, commerce_product_variation.type, commerce_stock_transaction.entity_id, commerce_stock_transaction.entity_type, commerce_product__field_brand_field_brand_value, commerce_product_field_data_title, commerce_product_field_data.product_id\nHAVING (SUM(commerce_stock_transaction.qty)  )\nORDER BY commerce_product__field_brand_field_brand_value ASC, commerce_product_field_data_title ASC; Array\n(\n    [:db_condition_placeholder_4] => 1\n    [:db_condition_placeholder_5] => flower\n    [:db_condition_placeholder_6] => 0\n    [:db_condition_placeholder_7] => 0\n    [:db_condition_placeholder_8] => 0\n    [:db_condition_placeholder_9] => 0\n    [:db_condition_placeholder_10] => 0\n    [:db_condition_placeholder_11] => 0\n    [:db_condition_placeholder_12] => 1\n    [:views_join_condition_0] => 0\n    [:views_join_condition_1] => 0\n    [:views_join_condition_3] => flower\n)\n" at /home/rcheung/workspace/cannabudexpress/web/core/modules/views/src/Plugin/views/query/Sql.php line 1543, referer: https://www.default.com/admin/structure/views/view/menu

I see that it doesn't seem to be adding the table name for the transaction table when doing the JOIN ON entity_id = variation_id:

LEFT JOIN {commerce_product_variation} commerce_product_variation ON .entity_id = commerce_product_variation.variation_id 

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.