Rails 3: Including an SQL function in a query

Question

MyModel.select('a, b, c').all

This returns the following from a database:

+---+---+---+
| a | b | c |  
+---+---+---+
| 1 | 2 | 3 |
| 4 | 5 | 6 |
| 7 | 8 | 9 |
+---+---+---+

Question: How would I include a fourth column with the result of an SQL function?

i.e.

+---+---+---+---------------------+
| a | b | c | MYFUNCTION(a, b, c) |
+---+---+---+---------------------+
| 1 | 2 | 3 | 123                 |
| 4 | 5 | 6 | 456                 |
| 7 | 8 | 9 | 789                 |
+---+---+---+---------------------+

The following didn't work:

MyModel.select('a, b, c, MYFUNCTION(a, b, c)').all

although, if I use AS to give the column the name of a valid model attribute, it works:

MyModel.select('a, b, MYFUNCTION(a, b, b) AS c').all

+---+---+-----+
| a | b |  c  |
+---+---+-----+
| 1 | 2 | 122 |
| 4 | 5 | 455 |
| 7 | 8 | 788 |
+---+---+-----+

I would prefer to solve this within the context of the model rather than reverting to raw SQL as I also need to utilise scopes.

Any suggestions much appreciated.

Dogbert · Accepted Answer · 2011-01-21 22:42:42Z

2

You can give any arbitrary name to the AS column, instead of giving an existing attribute.

irb(main):009:0> u = User.select('id, id+1 as blah').first
=> #<User id: 1>
irb(main):010:0> u.id
=> 1
irb(main):011:0> u.blah
=> 2
irb(main):012:0>

answered Jan 21, 2011 at 22:42

Dogbert

224k43 gold badges419 silver badges416 bronze badges

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

1 Comment

gjb Over a year ago

Great, so you can! I was confused as this wasn't visible in the returned model (the response to line 009:0 above). Thank you.

Collectives™ on Stack Overflow

Rails 3: Including an SQL function in a query

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related