2

I am programming a custom import system with Magento libraries, but when I import configurable products I get an SQL error.

This is my configurable product function (only a part of my code):

function createConfigurableProduct($product, $objectManager, $_product) {

    // 1. Get swatches attributes
    $attr_swatches = explode("#", $product["configurable_attributes"]);
    $attr_ids = array();
    $attr_options = array();
    foreach ($attr_swatches as $attr_sw) {
        $attribute = $objectManager->create('\\Magento\\Catalog\\Model\\Product\\Attribute\\Repository')
            ->get($attr_sw);
        array_push($attr_ids, $attribute->getId());
        $attributeOptionAll = $objectManager->get(\Magento\Eav\Model\ResourceModel\Entity\Attribute\Option\Collection::class)
            ->setPositionOrder('asc')
            ->setAttributeFilter($attribute->getId())
            ->setStoreFilter()
            ->load();
        array_push($attr_options, $attributeOptionAll);
    }

    // 2. Select configurable attributes
    $_product->getTypeInstance()->setUsedProductAttributeIds($attr_swatches, $_product);

    // 2. prepare information for each simple product
    $configurableAttributesData = $_product->getTypeInstance()->getConfigurableAttributesAsArray($_product);
    $_product->setConfigurableAttributesData($configurableAttributesData);

    $simple_products = explode("#", $product["simples_skus"]);
    $simple_ids = array();

    $configurableProductsData = array();
    foreach ($simple_products as $prod) {

        $productId = $objectManager->get('Magento\Catalog\Model\Product')->getIdBySku($prod);
        array_push($simple_ids, $productId);
        $simple_product = $objectManager->get('\Magento\Catalog\Model\Product')->load($productId);

        // VISIBILITY_NOT_VISIBLE = 1
        // VISIBILITY_IN_CATALOG = 2
        // VISIBILITY_IN_SEARCH = 3
        // VISIBILITY_BOTH = 4

        $simple_product->setVisibility(1);
        $simple_product->save();

        $i = 0;
        $configurableProductsData['' . $productId] = array(); //['920'] = id of a simple product associated with this configurable
        foreach ($attr_swatches as $attr) {
            $attr_value = $simple_product->getData($attr);
            //$optionText = $attribute->getSource()->getOptionText($attr_value);
            var_dump($attr_value);
            //var_dump($optionText);

            $value_index = getOptionIDByCode($attr_options[$i], $attr_value);

            $productData = [
                'label' => $attr_value, //attribute label
                'attribute_id' => $attr_ids[$i], //attribute ID of attribute 'color' in my store
                'value_index' => $value_index, //value of 'Green' index of the attribute 'color'
                'is_percent' => '0', //fixed/percent price for this option
                'pricing_value' => '0' //value for the pricing
            ];

            $configurableProductsData[$productId] = $productData;
            $configurableAttributesData[$i]['values'][] = $productData;
            $i++;
        }
    }

    echo "configurableProductsData<pre>";
    print_r($configurableProductsData);
    echo "</pre>";

    echo "configurableAttributesData<pre>";
    print_r($configurableAttributesData);
    echo "</pre>";

    // 4. set data in 2 required formats
    $_product->setConfigurableProductsData($configurableProductsData);
    $_product->setConfigurableAttributesData($configurableAttributesData);

    // 5. save product with special flag
    $_product->setCanSaveConfigurableAttributes(true);
    $_product->save();

    //N.B. We need to have the same attribute set in both configurable product and it's associated product.
}

The error I get is:

Fatal error:  Uncaught PDOException: SQLSTATE[42S22]: Column not found: 1054 Unknown column 'entity_value.value' in 'field list' in /var/www/html/vendor/magento/zendframework1/library/Zend/Db/Statement/Pdo.php:233
Stack trace:
#0 /var/www/html/vendor/magento/zendframework1/library/Zend/Db/Statement/Pdo.php(233): PDOStatement->execute(Array)
#1 /var/www/html/lib/internal/Magento/Framework/DB/Statement/Pdo/Mysql.php(93): Zend_Db_Statement_Pdo->_execute(Array)
#2 /var/www/html/vendor/magento/zendframework1/library/Zend/Db/Statement.php(303): Magento\Framework\DB\Statement\Pdo\Mysql->_execute(Array)
#3 /var/www/html/vendor/magento/zendframework1/library/Zend/Db/Adapter/Abstract.php(480): Zend_Db_Statement->execute(Array)
#4 /var/www/html/vendor/magento/zendframework1/library/Zend/Db/Adapter/Pdo/Abstract.php(238): Zend_Db_Adapter_Abstract->query('SELECT `entity`...', Array)
#5 /var/www/html/lib/internal/Magento/Framework/DB/Adapter/Pdo/Mysql.php(541): Zend_Db_Adapter_Pdo_Abstract->query('SELECT `entity`...', Array)
#6 /var/www/ in /var/www/html/vendor/magento/zendframework1/library/Zend/Db/Statement/Pdo.php on line 240

If I add in Pdo.php var_dumps in order to show all the executed queries I get which query fails:

SELECT `entity`.`sku`, `product_entity`.`entity_id` AS `product_id`, `attribute`.`attribute_code`, `entity_value`.`value` AS `value_index`, `attribute_label`.`value` AS `super_attribute_label`, IFNULL(option_value.value, default_option_value.value) AS `option_title`, `default_option_value`.`value` AS `default_title` FROM `catalog_product_super_attribute` AS `super_attribute`
 INNER JOIN `catalog_product_entity` AS `product_entity` ON product_entity.entity_id = super_attribute.product_id
 INNER JOIN `catalog_product_super_link` AS `product_link` ON product_link.parent_id = super_attribute.product_id
 INNER JOIN `eav_attribute` AS `attribute` ON attribute.attribute_id = super_attribute.attribute_id
 INNER JOIN `catalog_product_entity` AS `entity` ON entity.entity_id = product_link.product_id
 INNER JOIN `catalog_product_entity` AS `entity_value` ON entity_value.attribute_id = super_attribute.attribute_id AND entity_value.store_id = 0 AND entity_value.entity_id = entity.entity_id
 LEFT JOIN `catalog_product_super_attribute_label` AS `attribute_label` ON super_attribute.product_super_attribute_id = attribute_label.product_super_attribute_id AND attribute_label.store_id = 0
 LEFT JOIN `eav_attribute_option` AS `attribute_option` ON attribute_option.option_id = entity_value.value
 LEFT JOIN `eav_attribute_option_value` AS `option_value` ON option_value.option_id = entity_value.value AND option_value.store_id = 1
 LEFT JOIN `eav_attribute_option_value` AS `default_option_value` ON default_option_value.option_id = entity_value.value AND default_option_value.store_id = 0
 INNER JOIN `cataloginventory_stock_status` AS `stock` ON stock.product_id = entity.entity_id WHERE (super_attribute.product_id = 32) AND (attribute.attribute_id = '') AND (stock.stock_status = 1) ORDER BY `attribute_option`.`sort_order` ASC

EDIT: The attribute I use for swatches in the test I made is created in an UpgradeSchema.php like this:

$eavSetup->addAttribute(
            ProductAttributeInterface::ENTITY_TYPE_CODE,
            'mycustomer_ip',
            [
                'type' => 'varchar',
                'backend' => '',
                'frontend' => '',
                'label' => 'mycustomer_ip',
                'input' => 'select',
                'class' => '',
                'global' => ScopedAttributeInterface::SCOPE_GLOBAL,
                'visible' => true,
                'required' => false,
                'user_defined' => true,
                'default' => "",
                'searchable' => false,
                'filterable' => true,
                'comparable' => true,
                'visible_on_front' => true,
                'used_in_product_listing' => true,
                'unique' => false,
                'apply_to' => '',
                'system' => 1,
                'group' => 'My Attributes Group',
                'option' => array('values' => [
                    '',
                    'IP00',
                    'IP20',
                    'IP22',
                    'IP40',
                    'IP42',
                    'IP43',
                    'IP44',
                    'IP65',
                    'IP66',
                    'IP67',
                    'IP68'
                ])
            ]
        );
  • Look at the attributes you are loading in $attr_swatches. It looks like one of the attributes has the backend_type = 'static' and therefore the wrong table is taken for getting the options ( catalog_product_entity instead of ``catalog_product_entity_int`). – HelgeB Mar 26 at 9:13
  • @HelgeB Thanks for your answer. I have edited my question with the UpgradeSchema code that creates the custom attribute that I use for swatches in the test I made. Any clue? – Ángel Carlos del Pozo Muela Mar 26 at 9:27
0

The Error Code 1054 says there is something wrong with your SQL syntax. And It couldn't find a column from the table that you have specified. Let's say,

`catalog_product_entity` AS `entity_value`  

Then you called the column value through entity_value.value and the SQL gives you an error that says it cannot find the column value in table catalog_product_entity.

Double check your SQL syntax and change what needs to be changed and your import will work the way you expected it to work.

  • The query is not mine, its origin is Magento core. I think the same as @HelgeB, that Magento is using the wrong table. – Ángel Carlos del Pozo Muela Mar 27 at 10:05

Your Answer

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

Not the answer you're looking for? Browse other questions tagged or ask your own question.