1

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

5

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

  • 1
    I will underline here: DON'T ACCESS THE DATABASE DIRECTLY(!) :-) – Fabian Blechschmidt Apr 12 '13 at 15:13
  • DON'T ACCESS THE DATABASE - there. I think it's clear :) – philwinkle Apr 12 '13 at 15:15
  • It can not be mentioned enough ;) – Fabian Blechschmidt Apr 12 '13 at 15:23
  • 1
    Why ? I have some functionalities on my magento website with new tables on the database made by me. Anyway, Thanks @philwinkle for the extended answer! :) – inavas Apr 12 '13 at 15:36
  • 2
    @inavas Magento developer shy away from using RAW SQL to access the database because, in the past, Magento has changed how certain items are stored in the database, or you may thing you're reading the data from the correct place, but you're actually not. – Alan Storm Apr 12 '13 at 17:26

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.