Magento – stored the group prices on magento database

backenddatabaseproductproducts-management

I'm developing a module in backend that displays the 'group prices' of the products, but I don't know where are them stored in the database in order to show them. If anyone has some idea I will appreciate it.

Thanks

Best Answer

Firstly, displaying the price of a grouped product is trivial:

$product = Mage::getModel('catalog/product')->load($product_id);
echo $product->getPrice();

Secondly, where it is stored and how it is calculated is not so much complex as it is undesirable. Accessing the database directly for pricing will not respect the custom options related to pricing that are set on the backend:

Display price before tax
Display "as low as" for configurables
Display in alternate currencies based on store view language config

So I suggest you not do this in your module. However for purely academic reasons, all pricing can be found for a product in one of two ways:

If you're using flat catalog

SELECT sku, price from catalog_product_flat_1 where sku='[your_grouped_sku]';

Note: the last _1 of the table name is generally the internal id of the store view code.

If you're not using flat catalog

This gets tricky because your eav attribute id for base pricing may be different than mine - Mine are entity_type_id = 4, attribute_id=64. Make sure to look yours up via attribute_code in eav_attribute table:

SELECT sku, price.value AS price
FROM catalog_product_entity 
LEFT JOIN catalog_product_entity_decimal price ON (price.`entity_type_id`=4) AND (price.`attribute_id`=64)
WHERE sku='[your sku]'

How you should be doing this

Use a custom collection that extends an existing collection - joining on your custom table. Because you haven't provided any direction here this is intended to be pseudo-code that will point you in the right direction. This is in no way complete and is not a drop-in fix to provide your own collection.

<?php

class YourCompany_Module_Model_Feature_Collection extends Mage_Core_Model_Mysql4_Collection_Abstract
{

    protected function _prepareCollection()
    {
        $collection = Mage::getModel('catalog/product')->getCollection();

        $your_table = $this->getTable('yourmodule/model');

        $collection->getSelect()
            ->joinLeft(array('a'=>$your_table),'a.entity_id=main_table.entity_id');

        return $collection;
    }
}

For more on creating and working with custom collections see these articles:

http://alanstorm.com/magento_models_orm

http://alanstorm.com/magento_collections

Related Solutions

Magento – Magento doesn’t displays group price

This is actually by design.

Magento will show the minimum price, because the retail price should always be higher then the group prices (otherwise, why wouldn't the customer just not login then attempt to checkout).

This is evident in the following function:

    /**
 * Apply group price for product
 *
 * @param Mage_Catalog_Model_Product $product
 * @param float $finalPrice
 * @return float
 */
protected function _applyGroupPrice($product, $finalPrice)
{
    $groupPrice = $product->getGroupPrice();
    if (is_numeric($groupPrice)) {
        $finalPrice = min($finalPrice, $groupPrice);
    }
    return $finalPrice;
}

Source: app/code/core/Mage/Catalog/Model/Product/Type/Price.php

So running through you scenario above, using EE 1.13, I logged into a customer account. The regular price of the product was $150. The retailer group price I set as $250, and the wholesale group price I set as $125. The wholesale displayed as $125, however the retailer group price was $150. Again, this is as design, it's not a bug but a feature.

Backend Prices Wholesale Pricing Retailer Pricing

You can also try the logic by trying to add a "special price" that is greater then the regular price. The special price won't show.

Solutions for your needs.

Make sure your regular price is always higher then groups
Possibly create an extension that extends the logic in app/code/core/Mage/Catalog/Model/Product/Type/Price.php (this may not be the only file you need to extend, however it is the file with the majority of the pricing logic).

If you do end up creating your own extension, always remember to never edit core code.

EAV Attribute Values Stored in Database

Whenever you save attribute from the admin panel, Magento will run validation on it and it will change your backend type based on input type. You can see that in getBackendTypeByInput method inside Mage_Adminhtml_Catalog_Product_AttributeController class.

   /**
     * Detect backend storage type using frontend input type
     *
     * @return string backend_type field value
     * @param string $type frontend_input field value
     */
    public function getBackendTypeByInput($type)
    {
        $field = null;
        switch ($type) {
            case 'text':
            case 'gallery':
            case 'media_image':
            case 'multiselect':
                $field = 'varchar';
                break;

            case 'image':
            case 'textarea':
                $field = 'text';
                break;

            case 'date':
                $field = 'datetime';
                break;

            case 'select':
            case 'boolean':
                $field = 'int';
                break;

            case 'price':
                $field = 'decimal';
                break;
        }

        return $field;
    }

You can use this as a guideline when adding new attributes via install script. If you are adding attribute via admin you don't have to worry about that. Of course, if you modify backend or frontend type directly from DB, Magento will validate and 'fix' the attribute on next save.

In other words you should never, ever change attribute properties directly from DB. Especially if the attribute already has some values saved. Doing this will result in attribute values being written in different entity type tables, which is what happened in your case. This will result in various issues, e.g. attribute disappearing from layered navigation, not searchable or filterable, cannot be saved, etc.

Fix for this is simple. First you need to determine what your backend/input types are. Then you need to make sure that the combination is valid using the code above and that it won't be changed in the future by manual editing.

Let's assume you want decimal/price. This means that your values should be saved in catalog_product_entity_decimal table. You will have to check all the other catalog_product_entity_* tables and remove all entries associated with your attribute_id. If you need to preserve the data you can also export rows before you delete them (without value_id) and import them in catalog_product_entity_decimal table.

This should fix your attribute. Remember to reindex after this is done.

Best Answer

Related Solutions

Magento – Magento doesn’t displays group price

EAV Attribute Values Stored in Database

Related Topic