Howto change search query using list "order by" on Search result page

[SmaRTeY]

Hi,

at this moment if you change the list order on search result page to lowest price first the result is displaying the "check with seller" ads first. I want these to be displayed last so the results show first 'Free' and then 0,01cents and up and last the 'check with seller' items.

Now, the order itself is done on price so by adding the condition >= 0 the results are shown starting with Free ads but then there are no 'check with seller' ads in the result. So to overcome this I think I should add a virtual field that considers 'check with seller' as highest price possible in Osclass meaning they are used in result but always will end up last when ordering lowest price first.

HOWEVER, in case of highest price order first I want this to be the other way around ie. true high prices first then the free items and then the 'check with seller' ads. This is how it actually works so no change is needed here.
But afaik it does mean that the query change has to be dynamically set in case of lowest price order first.

I found some SQL that shows me how to create the virtual field:
http://stackoverflow.com/questions/455261/how-to-create-virtual-column-using-mysql-select
 
Now I am wondering what would be the best approach to achieve this looking at the "order by" process and available search manipulation options.
Should I use javascript and an 'onchange' method of the 'order by' dropdown to manipulate the Query?
Should this be done using 'addcondition' or is there another / better approach?
Is there a way I can make only one query change related to the order by with the three order by options or do I have to make another 'revert' query in case of the two other order by options?


Regards,
Eric

[teseo]

Hi,

For "Lower Price first":

Code: [Select]

ORDER BY COALESCE(i_price, 99999999999999999999) ASC, dt_pub_date DESC

I've added a secondary order by publishing date for those "Check with seller" (NULL)

For "Higher Price first":

Code: [Select]

ORDER BY COALESCE(i_price, NULL) DESC, dt_pub_date DESC

Regards

[SmaRTeY]

Hi teseo.

I had to look at this a bit before I understood, so I can just add this as a 'order-by condition' specifically on the price field.
Pretty cool, thanks for helping me finish my quest

Pretty sure a lot of others find this 'order-by change' interesting as well.

Regards,
Eric

[SmaRTeY]

Well, I was speaking too soon, I am having trouble adding the order by to the search teseo.
Maybe my brains are playing tricks on me or I am simply lacking knowledge.....a bit blurry here

Hi teseo.

I had to look at this a bit before I understood, so I can just add this as a 'order-by condition' specifically on the price field.
Pretty cool, thanks for helping me finish my quest

Pretty sure a lot of others find this 'order-by change' interesting as well.

Regards,
Eric

[teseo]

I think this is it:

Code: [Select]

<?php
function cust_modify_order_by_price() {

    if (Params::getParam('sOrder') == 'i_price') {
        if (Params::getParam('iOrderType') == 'asc') $order = 'COALESCE(i_price, 99999999999999999999) ASC, dt_pub_date';
        else $order = 'COALESCE(i_price, NULL) DESC, dt_pub_date';

        Search::newInstance()->order($order, 'DESC');
    }
}

osc_add_hook('search_conditions', 'cust_modify_order_by_price');
?>

Regards

[SmaRTeY]

I think so too.... I was kinda having the same code but I did not notice a missing capital in the var I was using so I kept getting 500's
In my functions.php I have collected several search modifications so the code looks a bit better organised which in itself is good but then I should use the correct written var!......sigh (another hour wasted even WITH having your code answer below) The only excuse  I have is lack of sleep...

I think this is it:

Code: [Select]

<?php
function cust_modify_order_by_price() {

    if (Params::getParam('sOrder') == 'i_price') {
        if (Params::getParam('iOrderType') == 'asc') $order = 'COALESCE(i_price, 99999999999999999999) ASC, dt_pub_date';
        else $order = 'COALESCE(i_price, NULL) DESC, dt_pub_date';

        Search::newInstance()->order($order, 'DESC');
    }
}

osc_add_hook('search_conditions', 'cust_modify_order_by_price');
?>

Regards