How to Build an SQL Query for Products with Custom Options in Magento

custom-optionsquerysql

Need help making a query which returns the Product ID of all products which have custom options

Best Answer

Magento:

$options = Mage::getModel('catalog/product_option')->getCollection();
$options->addFieldToSelect('product_id');
$options->getSelect()->distinct(true);

foreach ($options as $option) {
    Zend_Debug::dump($option->getProductId());
}

Query:

SELECT DISTINCT `main_table`.`product_id` FROM `catalog_product_option` AS `main_table`

Related Solutions

Magento – Delete custom options from products in specific store

The custom options are stored in the table catalog_product_option. If you delete all the options for the product_ids that you specify.

Magento Grouped Products – Simple Products with Custom Options

I managed to allow custom options on bundle products by using the following code (it does edit some core files and there are a few paid extensions that achieve similar functionality):

Edit for file app/code/core/Mage/Bundle/Model/Observer.php, namely just comment some code out as follows:

/*
        if ($product->getPriceType() == '0' && !$product->getOptionsReadonly()) {
            $product->setCanSaveCustomOptions(true);
            if ($customOptions = $product->getProductOptions()) {
                foreach (array_keys($customOptions) as $key) {
                    $customOptions[$key]['is_delete'] = 1;
                }
                $product->setProductOptions($customOptions);
            }
        }
*/

To get rid of “Bundle with dynamic pricing cannot include custom defined options. Options will not be saved.” message you should hack app/design/adminhtml/default/default/template/catalog/product/edit/options.phtml template:

//show error message
/*
if ($('price_type')) {
    if ($('price_type').value == '0' && $('dynamic-price-warrning')) {
        $('dynamic-price-warrning').show();
    }
}
*/

I found this in the following thread on Magento's forums: http://www.magentocommerce.com/boards/viewthread/14910/P15/#t208213

I then required defaults to be set for the custom option on bundle products (and other products that used listed options) and used this extension to implement the functionality: https://github.com/magebuzz/Magebuzz_Customoption

Best Answer

Related Solutions

Magento – Delete custom options from products in specific store

Magento Grouped Products – Simple Products with Custom Options

Related Topic