Convert ActiveRecord statement into postgresql query- Rails 4/postgresql 9.4

Question

How do you translate the following Active Record query into raw postgresql ?

I just want to convert the line with '@available_deal =Deal where...'

app/controllers/deals_controller.rb

def show_deals_available
    @deal = Deal.friendly.find params[:id] # no need to turn this into postgresql
    @available_deal = Deal.where('deal_id = ? AND deal_status = ?',
                         @deal.id, "not_yet_taken").first   
    respond_to do |format|
      format.js 
    end
  end

EDIT thanks to feedback

I went on my local console and copied/pasted the query in my files and just replaced the number by my variables like (@deal.id)

So now I have:

app/controllers/deals_controller.rb

def show_deals_available
    @deal = Deal.friendly.find params[:id] # no need to turn this into postgresql
    @available_deal = Deal.where('deal_id = ? AND deal_status = ?',
                         @deal.id, "not_yet_taken").first
    @available_deal  = SELECT "deals".* FROM "deals" WHERE (deal_id = @deal.id AND deal_status = 'not_yet_taken') ORDER BY "deals"."id" ASC LIMIT 1


    respond_to do |format|
      format.js 
    end
  end

But I am getting an error:

SyntaxError - syntax error, unexpected tCONSTANT, expecting keyword_end
...ROM "deals" WHERE (deal_id = @deal.id AND ...
...                               ^
/deals_controller.rb:70: syntax error, unexpected tCONSTANT, expecting ')'
..." WHERE (deal_id = @deal.id AND deal_status = ...
...                               ^
....

Yeah, pasting a piece of SQL into a bunch of Ruby code. Nothing can go wrong, right? ;) What is the end goal you're after? — D-side
– D-side, Commented Sep 26, 2015 at 20:34
learn more sql and move to raw sql directly inside my controller — Mathieu
– Mathieu, Commented Sep 26, 2015 at 20:37
I found rails method find_by_sql maybe I can try this but is it like "real ra sql" or it it still active record, that will be less fats than real raw sql. sorry rookie here. — Mathieu
– Mathieu, Commented Sep 26, 2015 at 20:38
Actually, using plain SQL in Rails is discouraged. It tends to be longer, riskier and less maintainable. — D-side
– D-side, Commented Sep 27, 2015 at 11:19

panmari · Accepted Answer · 2015-09-26 20:47:51Z

2

You could just just take a look at the rails console when you execute your code. There you should find an entry that looks somewhat like

SELECT * FROM deals WHERE deal_id = 1 AND deal_status = 'not_yet_taken'

which is the corresponding line in SQL.

Edit:

You seem want to inject raw SQL in your ruby code. This is considered bad style for things that can be solved differently, since this may break when updating rails or changing your DB backend. Nevertheless, if you REALLY want to take this route, you could do something like

Deal.find_by_sql("SELECT \"deals\".* FROM \"deals\" WHERE (deal_id = #{@deal.id} AND click_win_throbber_status = 'not_yet_clicked') ORDER BY \"deals\".\"id\" ASC LIMIT 1"

edited Sep 26, 2015 at 20:47

answered Sep 26, 2015 at 20:09

panmari

3,8973 gold badges32 silver badges49 bronze badges

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

4 Comments

Mathieu Over a year ago

dumb me sorry i must be tired

Mathieu Over a year ago

Sorry but it did not work. I copy pasted the console query: I get an error, please check the edit I am going to write in a minute

Mathieu Over a year ago

i know it's not Railsy but this query will happen every second on a 10M row table or more, so trying to optimize it even if I need to depart from the Railsy way+disavantages if change of dB of course. Will try your answer now.

panmari Over a year ago

You should probably look into the topic of indexing rather than trying to optimize it trough these means.

Collectives™ on Stack Overflow

Convert ActiveRecord statement into postgresql query- Rails 4/postgresql 9.4

1 Answer 1

4 Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Related