Nested query in Rails without using find_by_sql

Question

My sql query is

SELECT MAX(sale_money)
FROM (
    SELECT SUM(sale_money) sale_money
    FROM sales
    WHERE MONTH(created_at) = 5
    GROUP BY user_id
) t

I write the subquery in Rails 4 code:

record = Sale.select("SUM(sale_money) AS sale_money").where("MONTH(created_at) = 5").group(:user_id)

Someone could teach me query the max_record based on record, or another query, except max_record = Sale.find_by_sql(my_raw_sql) or max_record = Sale.where(my_raw_sql)

Arup Rakshit · Accepted Answer · 2015-06-09 06:21:43Z

1

Yes, the same can be done using Activerecord form method.

Sale.from(Sale.select("SUM(sale_money) AS sale_money")
               .where("MONTH(created_at) = 5")
               .group(:user_id), :t
         )
    .select("MAX(t.sale_money) as sale_money")

But, you can do as :

Sale.select("SUM(sale_money)")
    .where("MONTH(created_at) = 5")
    .group(:user_id)
    .order("SUM(sale_money) DESC")
    .limit(1)

The above one don't need the 2 SELECT queries.

edited Jun 9, 2015 at 6:21

answered Jun 9, 2015 at 3:59

Arup Rakshit

119k30 gold badges270 silver badges328 bronze badges

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

10 Comments

user4557573 Over a year ago

the 2nd query is working. The 1st one may be wrong. Rails parse wrong SQL query: SELECT sales.* FROM Sale.where("MONTH(created_at) = 5").select("SUM(sale_money) AS sale_money").group(:user_id). Thank you

user4557573 Over a year ago

Arup Rails understand

'Sale.select("SUM(sale_money) AS sale_money")                .where("MONTH(created_at) = 5")                .group(:user_id)'

like a string. So, it parse your code to:

SELECT MAX(t.sale_money) AS sale_money FROM Sale.select("SUM(sale_money) AS sale_money").where("MONTH(created_at) = 5").group(:user_id)

Arup Rakshit Over a year ago

@user4557573 It is working here.. what you meant not working, I am not getting you..

user4557573 Over a year ago

Yes, I forgot removing the apostrophe ('...') in your first way. Thank for teaching me :)

user4557573 Over a year ago

Sorry for your time. And, I think you should update your 1st way with removing the apostrophe for other readers understand. I mean the 1st way should be

Sale.from(Sale.select("SUM(sale_money) AS sale_money")                .where("MONTH(created_at) = 5")                .group(:user_id), :t          )     .select("MAX(t.sale_money) as sale_money")

|

Collectives™ on Stack Overflow

Nested query in Rails without using find_by_sql

1 Answer 1

10 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

10 Comments

Your Answer

Sign up or log in

Post as a guest

Related