Rails4: likes total count: posts order with SQLite3 is OK but PostgreSQL not working

Question

There is no problem with sqlite 3 but 'PG :: GroupingError: ERROR' appears in posgresql's environment.

I want to display posts with a lot of total by using paginate's gem in the order of total of like, but it is displayed without problem in sqlite 3

posts_controller.rb

  def index
    @posts = Post.order(created_at: :desc).paginate(:page => params[:page], :per_page => 18)
  end

  def popular
    @posts = Post.joins(:likes).group(:post_id).order("count(*) desc").paginate(:page => params[:page], :per_page => 18)
  end

However, in the postgres environment, 'PG :: GroupingError: ERROR: column' posts.id 'must appear in the GROUP BY clause or be used in an aggregate function' will be displayed and an error will be displayed.

likes table's column is

• id
• user_id
• post_id
• created_at
• updated_at

posts table's column is

• id
• title
• content
• picture
• category_id
• latitude
• longitude
• user_id
• address
• created_at
• updated_at

Looking at the answer, it seemed like it worked by modifying it, but another error appeared

def popular
    @posts = Post.joins(:likes).group(:id).order("count(*) desc").paginate(:page => params[:page], :per_page => 18)
end

SELECT "posts".* FROM "posts" INNER JOIN "likes" ON "likes"."post_id" = "posts"."id" GROUP BY "posts"."id" ORDER BY count(*) desc LIMIT 18 OFFSET 0

Rendered posts/popular.html.erb within layouts/application

Completed 500 Internal Server Error in 6ms (ActiveRecord: 3.0ms)

ActionView::Template::Error (PG::AmbiguousColumn: ERROR: column reference "id" is ambiguous

LINE 1: SELECT COUNT() AS count_all, id AS id FROM "posts" INNER JO... : SELECT COUNT() AS count_all, id AS id FROM "posts" INNER JOIN "likes" > ON "likes"."post_id" = "posts"."id" GROUP BY "posts"."id"):

popular.html.erb

<div class="mdl-grid">
  <div class="mdl-cell mdl-cell--1-col"></div>
  <div class="mdl-cell mdl-cell--10-col">
    <h2 class="mdl-typography--display-2 mdl-typography--font-thin">Popular posts</h2>
    <div class="mdl-grid">
<% @posts.each do |post| %>
  <div class="mdl-cell mdl-cell--6-col">
    <div class="demo-card-square mdl-card mdl-shadow--2dp">
      <div class="category category-shibuya"><%= link_to post.category.name, post.category.name %>
      </div>
      <div class="mdl-card__title mdl-card--expand" style="background: url('<%= post.post_attachments.first.picture %>') bottom center no-repeat #fff;">
        <h2 class="mdl-card__title-text"><%= post.title %></h2>
      </div>
      <div class="mdl-card__supporting-text">
        <%= post.content.truncate(120, separator: '.')%>
      </div>
      <div class="mdl-card__actions mdl-card--border">
        <a href="<%= post_path(post.id) %>" class="mdl-button mdl-button--colored mdl-js-button mdl-js-ripple-effect" data-upgraded=",MaterialButton,MaterialRipple">
          View more
          <span class="mdl-button__ripple-container"><span class="mdl-ripple"></span></span></a>
        <span id="like-link<%= post.id %>" class="like_box">
          <%= render "likes/like_links", post: post %>
          </span>
      </div>
    </div>
  </div>
  <% end %>
</div>
<%= will_paginate @posts %>
</div>
<div class="mdl-cell mdl-cell--1-col"></div>
</div>

index.html.erb

<% provide(:title, "New posts") %>
<div class="mdl-grid">
  <div class="mdl-cell mdl-cell--1-col"></div>
  <div class="mdl-cell mdl-cell--10-col">
    <h2 class="mdl-typography--display-2 mdl-typography--font-thin">New posts</h2>
    <div class="mdl-grid">
<% @posts.each do |post| %>
  <div class="mdl-cell mdl-cell--6-col">
    <div class="demo-card-square mdl-card mdl-shadow--2dp">
      <div class="category category-shibuya"><%= link_to post.category.name, post.category.name %>
      </div>
      <div class="mdl-card__title mdl-card--expand" style="background: url('<%= post.post_attachments.first.picture %>') bottom center no-repeat #fff;">
        <h2 class="mdl-card__title-text"><%= post.title %></h2>
      </div>
      <div class="mdl-card__supporting-text">
        <%= post.content.truncate(120, separator: '.')%>
      </div>
      <div class="mdl-card__actions mdl-card--border">
        <a href="<%= post_path(post.id) %>" class="mdl-button mdl-button--colored mdl-js-button mdl-js-ripple-effect" data-upgraded=",MaterialButton,MaterialRipple">
          View more
          <span class="mdl-button__ripple-container"><span class="mdl-ripple"></span></span></a>
        <span id="like-link<%= post.id %>" class="like_box">
          <%= render "likes/like_links", post: post %>
          </span>
      </div>
    </div>
  </div>
  <% end %>
</div>
<%= will_paginate @posts %>
</div>
<div class="mdl-cell mdl-cell--1-col"></div>
</div>

like.rb

class Like < ActiveRecord::Base
  belongs_to :post
  belongs_to :user
end

post.rb

class Post < ActiveRecord::Base
  belongs_to :user
  belongs_to :category 
  validates :user_id, presence: true
  validates :title, presence: true  
  validates :content, presence: true 
  validates :category_id, presence: true 
  has_many :likes, dependent: :destroy 
  has_many :post_attachments
  attr_accessor :post_attachment_attributes
  acts_as_commontable 
  geocoded_by :address
  after_validation :geocode
  accepts_nested_attributes_for :post_attachments,allow_destroy: true, reject_if: :all_blank

  private

    def picture_size
      if picture.size > 5.megabytes
        errors.add(:picture, "should be less than 5MB")
      end
    end
end

_like_links.html.erb

<% if user_signed_in? %>
<% if current_user.likes.find_by(post_id: post.id) %>
     <%= link_to (content_tag(:i, '', class: 'fa fa-heart')),unlike_path(post.id), method: :delete, remote: true %>
      <%=  post.likes.count %> Like
  <% else %>
    <%= link_to (content_tag(:i, '', class: 'fa fa-heart-o')),like_path(post.id), method: :post, remote: true %>
     <%=  post.likes.count %>  Like
  <% end %>
<% else %>
  <%=  post.likes.count %>  Like
<% end %>

It will be very helpful to tell me

thank you

Answer 1

PG :: GroupingError: ERROR: column' posts.id 'must appear in the GROUP BY clause or be used in an aggregate function

You need to use id since you are calling group on Post, so there is no post_id available:

def popular
  @posts = Post.joins(:likes).group(:id).order("count(*) desc").paginate(:page => params[:page], :per_page => 18)
end

PG::AmbiguousColumn: ERROR: column reference "id" is ambiguous

likes table has a column id causing the resulting SQL SELECT COUNT() AS count_all, id AS id to be ambiguous since id is on both tables to be joined (so id could be taken either from likes table or posts table).

To fix this i recommend deleting id column from likes table, since that table seems to be used only as a connection for a many-to-many relationship between User and Post (more information here), so there is no need for id.

To do so, two things can be done:

1) Update likes create migration to avoid ids:

create_table :likes, id: false do |t|
  t.belongs_to :post, index: true
  t.belongs_to :user, index: true
end

You will have to delete your table (i.e. rollback) and run the migration again.

2) Create a new migration file to delete id column:

def change
  remove_column :likes, :id
end

I prefer going with first option if you are still on development so you have less migrations to deal with, but if you are in production (or have data you don't want to lose) then go with the second option.