Home How do I convert my postgres query to a rails object?
Reply: 3

How do I convert my postgres query to a rails object?

Bassman
1#
Bassman Published in 2017-09-13 11:17:33Z

The following code below works as a Postgre query, i'm trying to call it inside my rails app and assigning it to an object: @agent_monthly_performance.

SELECT user_id AS user_id,
       to_char(created_at,'Mon-YY') AS year_month,
       sum(total_amount) AS amount,
       sum(total_lm) AS lm
FROM   agent_sales
WHERE  created_at > NOW() - INTERVAL '1 year'
GROUP BY user_id, year_month"

Once that works, will I be able to call it via @agent_monthly_performance.year_month? I'm aware that i've created new columns that aren't in my schema via "AS" command. (ex. year_month).

Pardeep Saini
2#
Pardeep Saini Reply to 2017-09-14 04:34:20Z

you can use ActiveRecord::Base.connection.execute to execute row sql queries in rails

@agent_monthly_performance = ActiveRecord::Base.connection.execute("SELECT user_id AS user_id, to_char(created_at,'Mon-YY') AS year_month, sum(total_amount) AS amount, sum(total_lm) AS lm FROM   agent_sales WHERE  created_at > NOW() - INTERVAL '1 year' GROUP BY user_id, year_month").to_a
Bernie Chiu
3#
Bernie Chiu Reply to 2017-09-13 11:21:48Z

MyModel.find_by_sql(query) will still inherit model instance. You might need to check it further

NM Pennypacker
4#
NM Pennypacker Reply to 2017-09-13 17:38:04Z

You could do this:

sql = <<-SQL
  SELECT user_id AS user_id,
       to_char(created_at,'Mon-YY') AS year_month,
       sum(total_amount) AS amount,
       sum(total_lm) AS lm
  FROM   agent_sales
  WHERE  created_at > NOW() - INTERVAL '1 year'
  GROUP BY user_id, year_month
SQL

SomeModel.find_by_sql(sql)
You need to login account before you can post.

About| Privacy statement| Terms of Service| Advertising| Contact us| Help| Sitemap|
Processed in 0.307929 second(s) , Gzip On .

© 2016 Powered by cudou.com design MATCHINFO