CODEWITHSUNDEEP
ruby-on-railspostgresqlruby-on-rails-4activerecord
Mathieu
Mathieu

Reputation: 4787

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

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 = ...
...                               ^
....

Upvotes: 1

Views: 459

Answers (1)

panmari
panmari

Reputation: 3827

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"

Upvotes: 2

Related Questions