Rails Order by SQL

Question

I have a TraitQuestion model that have a number of traits or answers - each of which has a value such as 1, 2, 3, or -3105 (for none of the above). I can't change these values because they are required for an external API.

I'd like to order them by the value, but when I do that, the answer with -3105 shows up at the top even though it says "None of the above":

Answer 4 = -3105
Answer 1 = 1
Answer 2 = 2
Answer 3 = 3

Any idea on how we could order this so that it's

I'm pretty new to SQL but it seems like I should be able to do something like this:

@trait_question.traits.order('CASE WHEN value AS int > 0 then 1 ELSE 0 END')

But that doesn't work. Any ideas how I can adjust the order call SQL such that it would order them in the correct order?

EDIT: This is Postgresql 9.4.1 via Amazon AWS

you can try this way @trait_question.traits.order('CASE WHEN value AS int > 0 then value ELSE value*(-1) END') — Vishal Jain
– Vishal Jain, Commented Mar 19, 2016 at 7:39

Deepesh · Accepted Answer · 2016-03-19 14:12:01Z

7

I don't know if this will work for you or not but try this:

@trait_question.traits.order('ABS(value)')

The ABS will change the negative value to positive hence taking the absolute value each time. If your database field is string then you can do it like this as suggested by Tom which worked for him:

@trait_question.traits.order('ABS(CAST(value AS int))')

edited Mar 19, 2016 at 14:12

answered Mar 19, 2016 at 7:46

Deepesh

6,4381 gold badge28 silver badges45 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

TAH Over a year ago

Good try but no luck :)

Oskar Holmkratz Over a year ago

The above works for me on postgres 9.1. You can also try this: @trait_question.traits.order('@value') @ is shorthand for absolute in postgres.

TAH Over a year ago

This worked- my problem was that my column is a string so I had to add an additional CAST so @trait_question.traits.order('ABS(CAST(value AS int))') worked!!!

Vishal Jain · Accepted Answer · 2016-03-19 08:07:16Z

2

Hey you can try this way

@trait_question.traits.order("CASE WHEN (value AS integer > 0) THEN value ELSE (value As integer)*(-1) END")

Other wise use

@trait_question.traits.order("abs(value AS integer)")

edited Mar 19, 2016 at 8:07

answered Mar 19, 2016 at 7:44

Vishal Jain

1,9401 gold badge12 silver badges15 bronze badges

5 Comments

TAH Over a year ago

Unfortunately got a

PG::UndefinedFunction: ERROR:  operator does not exist: character varying > integer LINE 1: ...rait_question_id" = $1  ORDER BY CASE WHEN (value > 0) THEN ...

error with this

Vishal Jain Over a year ago

hey which database you are using? it perfectly worked on the mysql

TAH Over a year ago

postgresql > Amazon AWS

TAH Over a year ago

It seems like the abs(value AS integer) should work but I now get this...

PG::SyntaxError: ERROR:  syntax error at or near "AS" LINE 1: ...its"."trait_question_id" = $1  ORDER BY abs(value AS integer...

:(

Vishal Jain Over a year ago

try above option of case and put you complete error here

Jonny Rimek · Accepted Answer · 2016-03-19 08:13:34Z

1

I'm not sure what exactly you try to do. and I can't make a normal comment, but I'll give it a try.

can you go to your projekt folder run rails dbconsole

and execute

SELECT * FROM traitquestions ORDER BY value

This schould give you all entries ordered by the value, if you only want all possible values, run

SELECT value FROM traitquestions ORDER BY value GROUP BY value

if it works you can just create a method

def get_values
  find_by_sql "SELECT value FROM traitquestions ORDER BY value GROUP BY value"
end

and call this from the controller (or where ever you want)

edited Mar 19, 2016 at 8:13

answered Mar 19, 2016 at 8:04

Jonny Rimek

1,18113 silver badges20 bronze badges

Collectives™ on Stack Overflow

Rails Order by SQL

3 Answers 3

3 Comments

5 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

3 Comments

5 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related