CODEWITHSUNDEEP
ruby-on-railsactiverecord
Ran
Ran

Reputation: 3523

Rails / activerecords - group by and order by

I want to perform :group and :order on my module, since Mysql does group by before order by you get mixed results and not what you expected.

the SQL solution for that would be: 

SELECT * FROM 

(
select * from `my_table` order by timestamp desc
) as my_table_tmp

group by catid

order by nid desc

how can I write that in ruby using activerecord?

Upvotes: 0

Views: 1623

Answers (2)

tsherif
tsherif

Reputation: 11710

I think you can just use the Arel methods directly: 

MyTable.from("(SELECT * FROM my_table ORDER BY timestamp DESC) as my_table").group(:catid).order("nid DESC")

Upvotes: 1

shime
shime

Reputation: 9008

query = <<-sql_statement

  SELECT * FROM 

  (
  select * from `my_table` order by timestamp desc
  ) as my_table_tmp

  group by catid

  order by nid desc

sql_statement

result = MyTable.connection.execute(query)

You can also use ActiveRecord::Base.connection.execute(query).

To traverse through the records returned use result.each.

Example (from the mysql2 gem repo):

results.each do |row|
  # conveniently, row is a hash
  # the keys are the fields, as you'd expect
  # the values are pre-built ruby primitives mapped from their corresponding field types in MySQL
  # Here's an otter: http://farm1.static.flickr.com/130/398077070_b8795d0ef3_b.jpg
end

Upvotes: 0

Related Questions