How do I translate my SQLite3 query to postgreSQL?

Question

I am trying to order all the recipes in my database by the number of likes they have received. Likes are polymorphic and belong to :likeable while a recipe has many likes.

My query works for SQLite3, but when I upload to Heroku using PostgreSQL it seems to break things.

function is as follows:

Recipe.select('*').joins(:likes).group('recipes.id').order('COUNT(likes.likeable_id)')

And the error that Heroku gives me when I try to run the website:

ActionView::Template::Error (PG::GroupingError: ERROR:  column "likes.id" must appear in the GROUP BY clause or be used in an aggregate function

Everything compiles, but the homepage uses that scope function so I get a server error right away.

Answer 1

You need to explicitly select recipies.*:

Recipe.select(
         Recipe.arel_table[:*], 
         Likes.arel_table[:*].count.as('likes_count')
      )
      .joins(:likes)
      .group(:id)
      .order(:likes_count)

Selecting the count is really optional - you can skip .select entirely and just fetch the aggregate in the order clause:

Recipe.joins(:likes)
      .group(:id)
      .order(Likes.arel_table[:*].count)