order by ((( currency = 'rub' ) * cost ) + ((currency = 'usd') * cost * 1 / 0.033) + ((currency = 'eur') * cost * 1 / 0.025) ) asc limit 0 , 10

This sorting works by the cost of the goods (cost), taking into account the selected currency (currency)

Can you explain how this works in my case, but I don’t understand what is being multiplied here, and then it is added up? Because for me it is clear only if it were so order by cost asc

    3 answers 3

     имеем, грубо говоря 3 условия: сделано по принципу(( 1 ) 2 ) - где 1 - проверка валюты, если она соответствует условию, то //будет 1, иначе 0, т.е. перемножаемое(2) с этим действием обнуляется(в нашем случае это 1-о из 3-х слагаемых) (( currency = 'rub' ) * cost ) // если ячейка столбца currency имеет значение 'rub' //то просто сортируем по значению cost //В этих двух случаях, если ячейка currency имеет значение 'eur' либо 'usd' //то умножаем cost на повышающий коэффициент, т.е. грубо говоря, переводим в рубли. ((currency = 'usd') * cost * 1 / 0.033) ((currency = 'eur') * cost * 1 / 0.025)

    In short, this sample sorts by price, in rubles , without affecting the price of positions in foreign currency.

      Perversion over lax type conversion in mysql.

      First, this expression is calculated for each row of the result, then sorting is performed on this digit. The sorting is affected by all the fields mentioned in the expression, sorted by the result of the expression.

       ( currency = 'rub' )

      If the currency field is equal to the string 'rub', then true is obtained, otherwise false. Then try to multiply the value of the cost of this line. Multiplication is a numeric operation, so a boolean result is reduced to a number. If true - then to 1, false - to 0. And multiplication is performed. If the currency is matched with 'rub', then the result will be the value of the cost field, otherwise - 0.

      Similarly for other terms:

       ((currency = 'usd') * cost * 1 / 0.033)

      If the currency was usd, then 1 is multiplied by cost and multiplied by 1 and divided by 0.033. If the currency is not usd, then it will be 0, because multiplying zero by anything will be 0.

      As a result, the currency is checked in each term. If the currency does not match - then this term becomes 0.

      More explicitly, this can be written as a case:

       case currency when 'rub' then cost when 'usd' then cost / 0.033 when 'eur' then cost / 0.025 end
      • thank. The last question, but I didn’t understand it a bit: do I have a sorting page for the price ?sort=cost&tsort=asc Currency is not specified here. It turns out that this construct does not work in this query? - Sarkis Allahverdian
      • How SQL can be connected and, apparently, the query string is known to you. See how you build SQL. There may be no relationship at all. - Shallow

      It works very simply. If the comparison expression in parentheses is true, it is equal to one, if not, zero. You understand that only one of the three checks will return one. Accordingly, we add for this unit the value converted according. coefficient in rubles, and two zeros obtained by multiplying zero due to inequality by the coefficients for the other two currencies. At the exit, this will give a value in rubles, whatever the currency. And at this cost and sort. All in rubles, the sorting is correct.