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

Question

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).

Bernie Chiu · Accepted Answer · 2017-09-13 11:21:48Z

3

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

answered Sep 13, 2017 at 11:21

Bernie Chiu

2632 silver badges12 bronze badges

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

1 Comment

Bassman Over a year ago

This works but only for user_id, the attribute available in my table. Is there a way to output my three other SELECT requirements?

Pardeep Saini · Accepted Answer · 2017-09-14 04:34:20Z

1

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

edited Sep 14, 2017 at 4:34

answered Sep 13, 2017 at 11:42

Pardeep Saini

2,1121 gold badge18 silver badges31 bronze badges

2 Comments

Bassman Over a year ago

Your answer outputs this: => #<PG::Result:0x007fb3755e0030 status=PGRES_TUPLES_OK ntuples=3 nfields=4 cmd_tuples=3> Not really sure how to access that.

Pardeep Saini Over a year ago

you just need to add .to_a at the end. I update the answer.

NM Pennypacker · Accepted Answer · 2017-09-13 17:38:04Z

0

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)

edited Sep 13, 2017 at 17:38

answered Sep 13, 2017 at 11:47

NM Pennypacker

6,99211 gold badges38 silver badges41 bronze badges

4 Comments

Bassman Over a year ago

I tried doing this to my model: AgentSale. It doesn't produce an error but the output is: [#<AgentSale id: nil, user_id: 14>, #<AgentSale id: nil, user_id: 13>, #<AgentSale id: nil, user_id: 1>] It seems to only select the attributes that is in the model. What about the 3 other entries in my SELECT that has some computations to it, how can I get that to output?

NM Pennypacker Over a year ago

Looks like you need to add another table to your FROM statement. Currently you're only pulling from agent_sales, which I'm guessing is only a join table.

Bassman Over a year ago

All my data is in agent_sales, do you mean I need to add another agent_sales table and join them together?

NM Pennypacker Over a year ago

Not necessarily. I didn't see that you were adding 'columns' with an AS statement.

Collectives™ on Stack Overflow

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

3 Answers 3

1 Comment

2 Comments

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

2 Comments

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related