Category Archives: magento

Magento: Unquote database query elements

If you are in a hurry, then just scroll down to the end of the article to see my answer to the problem.

Sometimes you have to use complex queries for ex. collections, which will be really an ugly code. Since direct query is not a real option, we have to go with the Magento way. This is all nice and beautiful, until you realize, that your idea is not possible with the Magento-Zend selects. Lets see an example, first with the needed result:

SELECT `main_table`.*, (SELECT `b`.`name` FROM `names` AS `b` WHERE `b`.`customer_id` = `main_table`.`customer_id` ORDER BY `b`.`name` DESC LIMIT 1) AS `names_name`
FROM `sales_flat_order_grid` AS `main_table` 
WHERE `main_table`.`group_id` = 1

You can say that this is an ugly query which can be replaced with joins and maybe with other methods, but we are talking about the unquoting something from the query.
So, the query above translated to a collection will look something like:

$collection = Mage::getResourceModel("sales/order_grid_collection")
->addExpressionFieldToSelect("names_name", "(SELECT `b`.`name` FROM `names` AS `b` WHERE `b`.`customer_id` = `main_table`.`entity_id` ORDER BY `b`.`name` DESC LIMIT 1)")
->addFieldToFilter("`main_table`.`group_id`", "1");

Ugly, isn’t it? And if we echo out the resulting query with echo $collection->getSelect(), it will be even more uglier:

SELECT `main_table`.*, `(SELECT ``b```.```name`` FROM ``names``` AS ```b`` WHERE ``b``.``customer_id`` = ``main_table``.``entity_id`` ORDER BY ``b``.``name`` DESC LIMIT 1)` 
FROM `sales_flat_order_grid` AS `main_table` 
WHERE (`main_table`.`group_id` = '1')

Lets get rid of the double and triple quotes by changing our addExpressionFieldToSelect:

->addExpressionFieldToSelect("names_name", "(SELECT b.name FROM names AS b WHERE b.customer_id = main_table.entity_id ORDER BY b.name DESC LIMIT 1)")

Now it is much better, but still not ok:

SELECT `main_table`.*, `(SELECT b`.`name FROM names` AS `b WHERE b.customer_id = main_table.entity_id ORDER BY b.name DESC LIMIT 1)` 
FROM `sales_flat_order_grid` AS `main_table` 
WHERE (group_id = '1')

Our whole subselect is quoted and it is not aliased at all. Here comes the trick. We will have to use the Zend_Db_Expr for this and we will put this new column directly into our select with $collection->getSelect()->columns() method. See it how:

$collection = Mage::getResourceModel("sales/order_grid_collection");
$collection->getSelect()->columns(array("names_name" => new Zend_Db_Expr("(SELECT b.name FROM names AS b WHERE b.customer_id = main_table.entity_id ORDER BY b.name DESC LIMIT 1)")));
$collection->addFieldToFilter("`main_table`.`group_id`", "1");

The resulting sql will be:

SELECT `main_table`.*, (SELECT b.name FROM names AS b WHERE b.customer_id = main_table.entity_id ORDER BY b.name DESC LIMIT 1) AS `names_name` 
FROM `sales_flat_order_grid` AS `main_table` 
WHERE (`main_table`.`group_id` = '1')

The only thing you need to be careful is to have your subselect between brackets, so your sql will remain valid.

Formatting price in Magento

There are a couple of methods to format price in Magento. The easiest and most used is:

Mage::helper("core")->currency($price, $format, $includeContainer)

Example:

echo Mage::helper("core")->currency(115, true, false)
//if your currency is Euro then output will be: €115.00

Sometimes you don’t need currency symbols in your prices, then you will need something like:

Mage::getModel('directory/currency')->setData("currency_code", Mage::app()->getStore(null)->getCurrentCurrency()->getCode())->format(
    $product->getFinalPrice(), 
    array('display' =>Zend_Currency::NO_SYMBOL), false);

The value for the display can be Zend_Currency::NO_SYMBOL (it will remove the symbol and show only the price) Zend_Currency::USE_SYMBOL (shows the currency symbol before the price), Zend_Currency::USE_SHORTNAME (shows the abbreviation of the currency before the price) or Zend_Currency::USE_NAME (shows the full name of the currency before the price). Example outputs:

Zend_Currency::NO_SYMBOL:         115.00
Zend_Currency::USE_SYMBOL:       €115.00
Zend_Currency::USE_SHORTNAME:  EUR115.00
Zend_Currency::USE_NAME:      EURO115.00

Adding, modifying or dropping a column in a table, the Magento way

This will be really simple. Just create a new install / upgrade script and write the following code into it:

$this->startSetup();
$this->getConnection()->addColumn($this->getTable('your_table_definition'), 'your column name', "column definition");
//example: $this->getConnection()->addColumn($this->getTable('catalog/product'), 'something', "INT(11) COMMENT 'this will create a column named something in catalog_product_entity table'");

$this->endSetup();

So, as you have already seen it, no more ALTER TABLEs.

The same is the situation with changing columns. Here are a couple of useful function declarations (for addColumn, changeColumn, modifyColumn, dropColumn):

    public function addColumn($tableName, $columnName, $definition, $schemaName = null)
    public function changeColumn($tableName, $oldColumnName, $newColumnName, $definition, $flushData = false, $schemaName = null)
    public function modifyColumn($tableName, $columnName, $definition, $flushData = false, $schemaName = null)
    public function dropColumn($tableName, $columnName, $schemaName = null)

The difference between the changeColumn and modifyColumn is that changeColumn modifies also the name of the column and of course the definition also (it uses ALTER TABLE %s CHANGE COLUMN %s %s %s), while modifyColumn changes only the definition (it uses ALTER TABLE %s MODIFY COLUMN %s %s).

Magento: How to create a timestamp table column with CURRENT_TIMESTAMP as default value?

Well, the easiest answer would be to use raw queries to create such tables. But that is not the Magento style. We will use the Magento style to build our table.

So, I suppose that you already created your install / upgrade script in your module. Now you have something like:

$this->startSetup();

//...

$this->endSetup();

In many cases you will meet with $installer = $this; and  with $installer->startSetup(), both are the same thing (it was created like this a long time ago, when such a notation influenced the autocomplete of the IDEs). The create table statement will be between the startSetup and the endSetup like:

$table = $this->getConnection()->newTable("your_table_definition")
->addColumn("column_name", Varien_Db_Ddl_Table::TYPE_TIMESTAMP, null, array(
"default" => "SOMETHING_DEFAULT"
), "some comment here")
->addColumn(...)
...
;
$this->getConnection()->createTable($table);

The question is what to put instead of SOMETHING_DEFAULT? Use Varien_Db_Ddl_Table::TIMESTAMP_INIT, so your addColumn definition will look like:

->addColumn("column_name", Varien_Db_Ddl_Table::TYPE_TIMESTAMP, null, array(
"default" => Varien_Db_Ddl_Table::TIMESTAMP_INIT
), "some comment here")

That’s it.

Magento: getChildHtml() not working

Do you want to use

echo $this->getChildHtml("mychild")

in your template and it doesn’t work? Do you have in your layout xml file something like:

<some_controller_action>
    <block type="some/block">
        <block type="other/block" name="mychild">
    </block>
>/some_controller_action>

Yes, this is really annoying. Add a name attribute to your some/block block, and it will instantly work! So the correct layout xml will look like:

 

<some_controller_action>
    <block type="some/block" name="somename">
        <block type="other/block" name="mychild">
    </block>
>/some_controller_action>

That's all folks!

Magento: Using Differing Setup Classes In One Module

I’m reposting Mike Whitby’s post:

On occasion you may have a module which requires both EAV and flat resource setup classes, in this situation you can instantiate a setup class as this note shows.

Eav:

$installer = Mage::getModel('eav/entity_setup','eav_setup');

Flat:

$installer = Mage::getResourceModel('core/setup','core_setup');

You can then continue with the setup script as you normally would.

 

Thank You Mike!

Why Magento Flex uploader won’t show your selected files?

If you hurry, then just scroll down until the end of the article to see the solution.

In the last couple of hours I tried to figure this out, because it was really ugly to select some files to upload and then see nothing selected (even if it was selected in the javascript object).
First of all I wanted to make myself sure that everything is configured well, so I’ve opened my previous article about how to use the Magento Flex uploader. Everything was nice, even after the second look. Then it got in my mind that the file uploading shows nicely the selected files before uploading. Compared the code… everything looked good. As a last try I began looking into the flash component’s source code and into the associated javascripts. And the miracle has happened. I simply couldn’t believe it. The reason why it was not working was the undefined maxUploadFileSize javascript variable. Incredible, isn’t it? Lets see the short explanation:

1. after pressing the browse button and when the files are selected, the handleSelect method will be called (we are talking about the Flex.Uploader javascript class which is located in flexuploader.js)

2. handleSelect looks like this:

handleSelect: function (event) {
            this.files = event.getData().files;
            this.checkFileSize();
            this.updateFiles();
            this.getInnerElement('upload').show();
            if (this.onFileSelect) {
                this.onFileSelect();
            }
        },

the problem occurs while trying to call the checkFileSize method.

3. lets see what is there:

checkFileSize: function() {
            newFiles = [];
            hasTooBigFiles = false;
            this.files.each(function(file){
                if (file.size > maxUploadFileSizeInBytes) {
                    hasTooBigFiles = true;
                    this.uploader.removeFile(file.id)
                } else {
                    newFiles.push(file)
                }
            }.bind(this));
            this.files = newFiles;
            if (hasTooBigFiles) {
                alert(
                    this.translate('Maximum allowed file size for upload is')+' '+maxUploadFileSize+".n"+this.translate('Please check your server PHP settings.')
                );
            }
        },

do you see the bolded variables? There are no checks whether the maxUploadFileSizeInBytes or the maxUploadFileSize is defined. When the code execution reaches these checks, it simply breaks the code execution, so handleSelect won’t ever run the remained code.

So for those who just came here and scrolled down for a quick solution:

Define maxUploadFileSizeInBytes and maxUploadFileSize javascript variables in your code, something like this:

    var maxUploadFileSizeInBytes = 2097152;
    var maxUploadFileSize = '2M';

That’s all.

Magento: Rows are not updated in custom EAV model tables

Another day, another annoying Magento issue. I’ve created a new EAV model, and when I thought that all things are finished, I’ve found out that in the model’s entity tables (_int, _varchar, etc.) the rows are not updated, but always inserted.
Viktor Hesselbom’s article pointed out that the problem’s root is in _updateAttribute method.
What remained to me is to fix it. You will have to override in your resource model the _updateAttribute method. Here is the solution:

 

protected function _updateAttribute($object, $attribute, $valueId, $value)
{
    $table = $attribute->getBackend()->getTable();
    if (!isset($this->_attributeValuesToSave[$table])) {
        $this->_attributeValuesToSave[$table] = array();
    }

    $entityIdField = $attribute->getBackend()->getEntityIdField();

    $data   = array(
        'entity_type_id'    => $object->getEntityTypeId(),
        $entityIdField      => $object->getId(),
        'attribute_id'      => $attribute->getId(),
        'value'             => $this->_prepareValueForSave($value, $attribute)
    );
    if ($valueId)
    {
        $data['value_id'] = $valueId;
    }

    $this->_attributeValuesToSave[$table][] = $data;

    return $this;
}

Only the value_id has been added to the $data array, if $valueId is set. In rest it is the same as the original method.
Good luck!

How to show the default checkbox near a Magento attribute

It is annoying that Magento doesn’t have a much easier way to insert this thing. What am I talking about? See this screenshot:

Default checkbox in product pages

Well, this is quite an ugly solution, but maybe it will work for you. First of all, on product pages there is a custom renderer for each element, that is why it is shown there. So, if you have the following element:

$name = $fieldset->addField('name', 'text', array(
    'name' => 'name',
    'required' => true,
    'class' => 'required-entry',
    'label' => Mage::helper('some_helper')->__('Name'),
));

you will have to render it with a custom renderer:

if ($name)
{
    $name->setRenderer(
        $this->getLayout()->createBlock('adminhtml/catalog_form_renderer_fieldset_element')
    );
}

At this point you should have the third column with the scope-label class. But the checkbox near it still won’t show up. For that we have to set the following for the form:

$storeObj = new Varien_Object();
$storeId = $this->getRequest()->getParam("store");
$storeObj->setId($storeId);
$storeObj->setStoreId($storeId);
$form->setDataObject($storeObj);

Now you should see also the checkbox. That’s all folks!

How to remove a tab from the Customer or Product page in the backend

I’ve been looking for this in google or stackoverflow for a couple of hours, before I found out how things work. We are talking about the backend. Lets begin with the Product tabs, because it is easier.

1. Removing Product tabs
a) Open your layout xml file (default: catalog.xml)
b) Search for <adminhtml_catalog_product_edit>
c) Add removeTab action Inside the “product_tabs” block

<reference name="left">
   <block type="adminhtml/catalog_product_edit_tabs" name="product_tabs">
      <action method="removeTab">
          <name>NAME_OF_TAB</name>
      </action>
   </block>
</reference>

You can find out the NAME_OF_TAB, by inspecting the tab’s anchor (<a>) and looking for the “name” attribute.

2. Removing Customer tabs
a) You have to override Mage_Adminhtml_Block_Customer_Edit_Tabs because the Magento guys did a small typo there: they are adding tabs in _beforeToHtml() method instead of _prepareLayout(). So first you have to modify your config.xml and add:

<global>
    <blocks>
        <adminhtml>
            <rewrite>
                <customer_edit_tabs>Yourmodule_Customer_Block_Edit_Tabs</customer_edit_tabs>
            </rewrite>
        </adminhtml>
    </blocks>
</global>

In Yourmodule_Customer_Block_Edit_Tabs just copy and paste the Mage_Adminhtml_Block_Customer_Edit_Tabs contents (don’t forget to change the class name!), and rename _beforeToHtml() method to _prepareLayout()

b) Add the removeTab action into your layout xml (default: customer.xml):

<adminhtml_customer_edit>
    <reference name="left">
        <block type="adminhtml/customer_edit_tabs" name="customer_edit_tabs">
            <action method="removeTab">
                <name>NAME_OF_TAB</name>
            </action>
        </block>
    </reference>
</adminhtml_customer_edit>

Finding out the NAME_OF_TAB is similar as for the products (see step 1).

That’s all folks!