Magento custom sort on grid – sort increment_id by numeric not alpha

You can easily filter column in a magento grid (see my previous post) but customize a sort is a little bit more hand made.

In this example in the order grid, I want to sort by increment_id, but this is a text fields and I want to sort it by numeric.

default sort : 1, 10, 100, 2, 3 …
numeric sort : 1, 2, 3, 10, 100 …

If you look the SQL request, you got this

SELECT `main_table`.* FROM `sales_flat_order_grid` AS `main_table` ORDER BY increment_id DESC LIMIT 20

To tell MySQL to sort this text column by numeric, you can add “+ 0″ to the ORDER BY clause, this will transform the text field into numeric field

SELECT `main_table`.* FROM `sales_flat_order_grid` AS `main_table` ORDER BY increment_id + 0 DESC LIMIT 20

Now, how to tell Magento to add “+ 0″ to this sort ?

Override your grid class and modify the “_setCollectionOrder($column)” function.

class Adin_Sales_Block_Adminhtml_Sales_Order_Grid extends Mage_Adminhtml_Block_Sales_Order_Grid {
    protected function _setCollectionOrder($column)
        $collection = $this->getCollection();
        if ($collection) {
            $columnIndex = $column->getFilterIndex() ? $column->getFilterIndex() : $column->getIndex();
            if($columnIndex == 'increment_id')
                $columnIndex = 'increment_id + 0';
            $collection->setOrder($columnIndex, strtoupper($column->getDir()));
        return $this;

doctrine2 random

For some really good reasons or, doctrine2 do not implement the RAND() fonction to sort randomly your query results. Here some dirty ways to do it. Don’t use them it’s bad.

One solution is to shuffle your collection of rows using php

      public function getRandom($max = 10,$site) {
        $results = $this->createQueryBuilder('u')
                ->where(' = :site')
                ->setParameter('site', $site)
                ->orderBy('u.sort', 'DESC')
        return $results;

In this solution, you retrieve the last 10 rows and shuffle them after. Peformances are not too bad but you will always retrieves the same last rows from your table.

Another solution is to use the array_rand php fonction

      public function getRandom($site) {
        $results = $this->createQueryBuilder('u')
                ->where(' = :site')
                ->setParameter('site', $site)
                ->orderBy('u.sort', 'DESC')
        $result2 = array_rand($results);
        return $result2;

In this case you fetch all rows from your table, this could be slow and memory consuming…

If you need to retrieve only one row, you can use somethinfg like this

 public function getOneRandom()
$em = $this->getEntityManager();
$max = $em->createQuery('
SELECT MAX( FROM questions q
return $em->createQuery('
SELECT q FROM questions q
WHERE >= :random

This solution can only be used if you want to retrieve any of the tables rows, if you add a filtrer you may return an empty result.

This solution is not dirty it just use 2 queries instead of one. the tips is to use the offset. And you can use a filter !!!

$qCount = Doctrine::getTable('Questions')
     ->where('site = :site')
     ->setParameter('site', $site)
     ->fetchOne(array(), Doctrine::HYDRATE_NONE);
$question = Doctrine::getTable('Questions')
     ->where('site = :site')
     ->setParameter('site', $site)
     ->offset(rand(0, $qCount[0] - 1))

And you still can use native queries :

MySQL custom sort

You are requesting some product on your database and you need to sort results by fixed column values ?
You can use the ORDER BY FIELD() clause.

SELECT * FROM product WHERE product_id IN (4, 2, 8, 9, 5) ORDER BY FIELD(product_id,4, 2, 8, 9, 5)


If you want to do some data loads in a Mysql DB regardless of insert or update statement you can use the INSERT … ON DUPLICATE KEY UPDATE request.

INSERT INTO test (firstname, lastname, skill)
      ('Alan', 'Cox', 98),
      ('Robert', 'Love', 85),
      ('Linus', 'Torvalds', 24),
      ('Rusty', 'Russell', 79)
     skill = VALUES(skill);

In this example, if the key is composed by firstname and lastname, when a duplicate key is detected, only the skill will be updated

You could find more informations on this statement here

Save MySQL query results into a file

Sometimes, phpMyAdmin doesn’t export queries properly. To get your file, you can add options to you query to save the result into a file.

SELECT cotisation.date_creation
FROM cotisation
WHERE cotisation.code_suppression =0
INTO OUTFILE '/home/transfert/cotisations_201212.csv'