CODEWITHSUNDEEP
ruby-on-railsruby-on-rails-4activerecordrails-activerecord
dsmorey
dsmorey

Reputation: 473

Rails: Omit order by clause when using ActiveRecord .first query?

I'm having a problem with a .first query in Rails 4 ActiveRecord. New behavior in Rails 4 is to add an order by the id field so that all db systems will output the same order.

So this...

Foo.where(bar: baz).first

Will give the query...

select foos.* from foos order by foos.id asc limit 1

The problem I am having is my select contains two sum fields. With the order by id thrown in the query automatically, I'm getting an error that the id field must appear in the group by clause. The error is right, no need for the id field if I want the output to be the sum of these two fields.

Here is an example that is not working...

baz = Foo.find(77).fooviews.select("sum(number_of_foos) as total_number_of_foos, sum(number_of_bars) as total_number_of_bars").reorder('').first

Here is the error...

ActiveRecord::StatementInvalid: PG::GroupingError: ERROR: column "foos.id" must appear in the GROUP BY clause or be used in an aggregate function LINE 1: ...bars FROM "fooviews" ORDER BY "...

Since the select is an aggregate expression, there is no need for the order by id, but AR is throwing it in automatically.

I found that I can add a reorder('') on to the end before the .first and that removes the order by id, but is that the right way to fix this?

Thank you

[UPDATE] What I neglected to mention is that I'm converting a large Rails 3 project to Rails 4. So the output from the Rails 3 is an AR object. If possible, the I would like the solution to keep in that format so that there is less code to change in the conversion.

Upvotes: 6

Views: 2127

Answers (1)

cschroed
cschroed

Reputation: 6899

You will want to use take:

The take method retrieves a record without any implicit ordering.

For example:

baz = Foo.find(77).fooviews.select("sum(number_of_foos) as total_number_of_foos, sum(number_of_bars) as total_number_of_bars").take

The commit message here indicates that this was a replacement for the old first behavior.

Upvotes: 12

Related Questions