Modify Rails controller to work with Postgres/Heroku

Question

I'm working my way through the railstutorial whilst working on my own mini project. Whilst it has been suggested that the development database you use should be the same as the one in production, the guide uses sqlite for development and I've been doing the same.

I've deployed some changes recently, that work locally, to my app hosted on Heroku however, when trying to access a specific page, I receive the following error:

If you are the application owner check the logs for more information.

How can I access the logs that are mentioned?
I'm guessing a possible cause is due to the code in my controller that returns data from the db, and works in sqlite but is possibly invalid for postgres. Can someone please suggestion improvements for the controller.

I've attached my code below.

accounts_controller.rb:

  # GET /accounts/1/history
  def history
    @account = Account.find(params[:id])

    @accountHistory = Account.select("accounts.*, balances.*").joins("JOIN balances").where('balances.account_id = :accountId', {accountId: @account.id}).where('accounts.id = :accountId', {accountId: @account.id}).order('date desc')
  end

history.html.erb

  <tbody>
      <% @accountHistory.each do |a| %>
        <tr>
            <td><%= date_format(a.date) %></td>
            <td class='account-balance'><%= number_to_currency(a.balance, unit: "£") %></td>
        </tr>
      <% end %>
  </tbody>

UPDATE:

Looking at the Heroku logs, it looks like there's a syntax error for Postgres. Can anyone suggest how to improve it?

heroku logs:

2015-12-22T16:54:39.361012+00:00 app[web.1]: Started GET "/accounts/3/history" for 2.220.200.172 at 2015-12-22 16:54:39 +0000
2015-12-22T16:54:39.370975+00:00 app[web.1]:   Account Load (0.9ms)  SELECT accounts.*, balances.* FROM "accounts" JOIN balances WHERE (balances.account_id = 3) AND (accounts.id = 3)  ORDER BY date desc
2015-12-22T16:54:39.371012+00:00 app[web.1]: LINE 1: ...ounts.*, balances.* FROM "accounts" JOIN balances WHERE (bal...
2015-12-22T16:54:39.371010+00:00 app[web.1]: PG::SyntaxError: ERROR:  syntax error at or near "WHERE"
2015-12-22T16:54:39.371013+00:00 app[web.1]:                                                              ^
2015-12-22T16:54:39.371015+00:00 app[web.1]: : SELECT accounts.*, balances.* FROM "accounts" JOIN balances WHERE (balances.account_id = 3) AND (accounts.id = 3)  ORDER BY date desc
2015-12-22T16:54:39.371797+00:00 app[web.1]:   Rendered accounts/history.html.erb within layouts/application (2.3ms)
2015-12-22T16:54:39.371929+00:00 app[web.1]: Completed 500 Internal Server Error in 6ms (ActiveRecord: 1.6ms)
2015-12-22T16:54:39.374155+00:00 app[web.1]: 
2015-12-22T16:54:39.374158+00:00 app[web.1]: ActionView::Template::Error (PG::SyntaxError: ERROR:  syntax error at or near "WHERE"

heroku logs will show you 100 lines of the log file. You can also tail it with heroku logs -t — Mark Swardstrom
– Mark Swardstrom, Commented Dec 22, 2015 at 17:29
@Swards Thanks. Looks like the syntax is invalid for Postgres. Any suggestions on how to resolve this? — Bhav
– Bhav, Commented Dec 22, 2015 at 17:37
What type of object is @accountHistory? For this particular error, you may just need to change joins("JOIN balances") to joins(:ballances) — Mark Swardstrom
– Mark Swardstrom, Commented Dec 22, 2015 at 17:45
@Swards Yes, I've just tried that and it works. If you post that as an answer, I'll mark it as accepted. — Bhav
– Bhav, Commented Dec 22, 2015 at 17:50
I think there may be more you can do to clean this up. You're doing a where clause, but only getting back one account record. Are the balance records really what you're after? — Mark Swardstrom
– Mark Swardstrom, Commented Dec 22, 2015 at 18:49

Mark Swardstrom · Accepted Answer · 2015-12-22 20:18:12Z

Looks like the Heroku error is related to the joins statement. It needs the on, which you can do a couple ways. To do this in an active relation, just change the joins statement from

.joins("JOIN balances")

To

.joins(:balances)

Note - this part may not be needed anymore

.where('balances.account_id = :accountId', {accountId: @account.id})

Looking at your code a little further, there may be something else you are really after. If accountHistory is a list of balance records, this would work for you....

@account = Account.find(params[:id])
@accountHistory = @account.balances

Or, to potentially do in one query...

@account = Account.includes(:balances).find(params[:id])

The balances collection is referenced on account, I suspect

class Account < ActiveRecord::Base

  has_many :balances

end

And, with the foreign key on Balance

class Balance < ActiveRecord::Base

  belongs_to :account

  # Add a scope if you like to reuse code
  scope :order_by_date, -> { order(:date => :desc) }

end

You can then reference balances easily. This is where Rails helps you out a lot to make things very readable.

@account.balances.order_by_date

You can assign this to @accountHistory if you want, or just refer to balances from the collection off account in the view which might look like:

<tbody>
  <% @account.balances.order(:date => :desc).each do |b| %>
    <tr>
      <td><%= date_format(b.date) %></td>
      <td class='account-balance'><%= number_to_currency(b.balance, unit: "£") %></td>
    </tr>
  <% end %>
</tbody>

yez · Accepted Answer · 2015-12-22 17:28:44Z

1

Did you run heroku run rake db:migrate -a <YOUR HEROKU APP NAME> after you deployed to heroku?

If you did, you can check the logs they are referring to with:

heroku logs --tail -a <YOUR HEROKU APP NAME>

Replace the <YOUR HEROKU APP NAME> with the name on your heroku configs. If you don't know how to find that, go to the heroku.com dashboard and find it under settings.

answered Dec 22, 2015 at 17:28

yez

2,37811 silver badges16 bronze badges

2 Comments

Bhav Over a year ago

Thanks. Looks like the syntax is invalid for Postgres. Any suggestions on how to resolve this?

yez Over a year ago

Try changing your query to only using ActiveRecord, something like

Account.select("accounts.*, balances.*").joins("JOIN balances").where(balances: { account_id: @account.id }).where(id: @account.id).order('date desc')

Collectives™ on Stack Overflow

Modify Rails controller to work with Postgres/Heroku

2 Answers 2

Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related