AdminController $this->_orderBy

[gary@yello]

Hello,

 

Im writing a backend module and woiuld like to order my list by multiple fields, how do I do this?

 

I have tried the following but the sql errors.

 
    $this->_orderBy = "a.iso, a.to";
    $this->_orderWay = 'DESC, ASC';
 
 

    $this->_orderBy = "a.iso||a.to";
    $this->_orderWay = 'DESC||ASC';
 
 
    $this->_orderBy = "a.iso!!a.to";
    $this->_orderWay = 'DESC!!ASC';

 

many thanks.

 

 

 

 

 

[trebara]

Hi Gary,

Because _orderBy and _orderWay are not arrays...

I think the only solution is:

creating a column to be used in $this->_orderBy (using concat in order to order by successive conditions) and set $this->_orderWay to either 'ASC' or 'DESC'.

 

Here is an exemple for my application:

        $this->_select .= ',
        concat("20",substr(deliv_date,11,2),substr(deliv_date,7,3),substr(deliv_date,4,4),substr(deliv_date,1,3),"-",30-current_state,"-",a.id_order) as my_order_by';
        $this->_orderBy = 'my_order_by';
        $this->_orderWay = 'DESC';

 

my_order_by is something like: 15-01-23-6-123 (date-current_state (reverse)-id_order)

As a result, rows are displayed in this order:

date DESC, current_state ASC, id_order DESC
Note that numeric values could be sorted in a reverse order.

In my example, I select 30-current_state (with 30 > max value).

Hope this helps.

Regards,

jean-marc
 

[PascalVG]

Haven't tried it, but you might 'hack' it by just adding it all to the orderby clause:

 

$this->_orderBy = "a.iso DESC, a.to ASC";
    $this->_orderWay = '';

 

 

(As you see, the DESC should be directly after the a.iso. )

When splitting it in $this->orderway as you did before, it will just adds it to the end of orderby text:

 

"a.iso, a.to DESC, ASC"

etc.

Which is incorrect

 

 

Give it a try.

My 2 cents

pascal

[trebara]

dear Pascal,

$this->_orderBy = "a.iso DESC, a.to ASC";

provides an error due to Validate::isOrderBy($order_by):

2167.
2168.         /* Check params validity */
2169.         if (!Validate::isOrderBy($order_by) || !Validate::isOrderWay($order_way)
2170.             || !is_numeric($start) || !is_numeric($limit)
2171.             || !Validate::isUnsignedId($id_lang))
2172.             throw new PrestaShopException('get list params is not valid');

 

after changing isOrderBy, in order to avoid this exception, a new problem appears:

ORDER BY clause is changed  in $this->_listsql and provides 1 SQL error: Unknown column 'a.to ASC' in 'order clause'

 

So, my conclusion is: only one field is allowed...

My solution works well and doesn't require any patch.

I think this one is ok for Gary because iso is a numeric value.

A solution for him could be:

$this->_select .= ', concat(1000000000000-a.iso,"-",a.to) as my_order_by';
        $this->_orderBy = 'my_order_by';
        $this->_orderWay = 'ASC';

jean-marc

[PascalVG]

Hi Jean-Marc,

 

Thanks for trying out my suggestion and your alternative solution.

 

Gary, did Jean-Marc's solution work for you? If so, please Mark his answer as 'the solution'.

 

Thanks,

pascal.