CODEWITHSUNDEEP
ruby-on-railspostgresqlsqliteactiverecordheroku-postgres
asdf
asdf

Reputation: 143

How do I translate my SQLite3 query to postgreSQL?

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.

Upvotes: 0

Views: 97

Answers (2)

max
max

Reputation: 101811

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)

Upvotes: 1

stwienert
stwienert

Reputation: 3632

You cannot select * from grouping by.

for most SQL-dabases (Postgres, newer Mysql, ...) you can only use SELET columns in a GROUP BY:

  1. columns you've grouped by, and that are transient by the grouped column (e.g. grouping recipes.id can also select recipes.title)
  2. And aggregated columns (count, sum, max)

Try:

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

Upvotes: 1

Related Questions