Re: [Rails] Using :group with #count generating bad SQL in Postgres

def self.order_by_number_of_comments_descending
    select('titles.id, titles.name, titles.whatever, count(comments.id) AS comments_count').
    joins(:comments).
    group('titles.id, titles.name, titles.whatever').
    order('comment_count DESC')
  end

Assuming titles model is like (id, name, whatever)

The result query expected is:

SELECT COUNT(*) AS comments_count, titles.id AS titles_id, titles.name as titles_name, titles.whatever as titles_whatever FROM "titles"
INNER JOIN "comments" ON "comments"."title_id" = "titles"."id" GROUP BY titles.id, titles.name, titles.whatever ORDER BY comments_count DESC

Atenciosamente,

Carlos Figueiredo

On Tue, Jul 23, 2013 at 1:48 PM, Yaw Boakye elGran <yawboakye10@gmail.com> wrote:

Can you write the right (no pun intended) query here? Thanks

On Tue, Jul 23, 2013 at 12:36 PM, Carlos Figueiredo <carlos.figueiredo87@gmail.com> wrote:

Your query is wrong...
You need group all columns you show that arent the result of an aggregate function...
So your group would be group('titles.*')

Em 22/07/2013 18:22, "yaw" <yawboakye10@gmail.com> escreveu:

Rails 4.0.0, Ruby 2.0

class Title < ...
  ...

  has_many :comments

  def self.order_by_number_of_comments_descending
    select('titles.*, count(comments.id) AS comments_count').
    joins(:comments).
    group('titles.id').
    order('comment_count DESC')
  end

results in a malformed SQL on Postgres,


SELECT COUNT(*) AS count_all, titles.id AS titles_id FROM "titles"
INNER JOIN "comments" ON "comments"."title_id" = "titles"."id" GROUP BY titles.id ORDER BY comments_count DESC

I've contacted @steveklabnik and he'd re-open an issue reporting this bug. In the meantime, has anybody got around this? The SQL is all correct until you call group.
Thanks for helping

--
You received this message because you are subscribed to the Google Groups "Ruby on Rails: Talk" group.
To unsubscribe from this group and stop receiving emails from it, send an email to rubyonrails-talk+unsubscribe@googlegroups.com.
To post to this group, send email to rubyonrails-talk@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/rubyonrails-talk/5def0eff-649b-4e00-9b87-154f02b9f34d%40googlegroups.com.
For more options, visit https://groups.google.com/groups/opt_out.
 
 

--
You received this message because you are subscribed to the Google Groups "Ruby on Rails: Talk" group.
To unsubscribe from this group and stop receiving emails from it, send an email to rubyonrails-talk+unsubscribe@googlegroups.com.
To post to this group, send email to rubyonrails-talk@googlegroups.com.

To view this discussion on the web visit https://groups.google.com/d/msgid/rubyonrails-talk/CANPOtXsquwiLear7OOO3ZJ-r-wzoO499L8JAhOVCWLNQGLXyPQ%40mail.gmail.com.

For more options, visit https://groups.google.com/groups/opt_out.
 
 

--
visit my blog @ yawboakye.blogspot.com     | call me on +233242057831 or +233273201210

--
You received this message because you are subscribed to the Google Groups "Ruby on Rails: Talk" group.
To unsubscribe from this group and stop receiving emails from it, send an email to rubyonrails-talk+unsubscribe@googlegroups.com.
To post to this group, send email to rubyonrails-talk@googlegroups.com.

To view this discussion on the web visit https://groups.google.com/d/msgid/rubyonrails-talk/CAKwoQDvcJkAuDiQHV9sH6%3DiaXdBz%3DAEZzxqrW0odBJi33Oyb%3Dw%40mail.gmail.com.

For more options, visit https://groups.google.com/groups/opt_out.
 
 

--
You received this message because you are subscribed to the Google Groups "Ruby on Rails: Talk" group.
To unsubscribe from this group and stop receiving emails from it, send an email to rubyonrails-talk+unsubscribe@googlegroups.com.
To post to this group, send email to rubyonrails-talk@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/rubyonrails-talk/CANPOtXvddWyJsBgHqV4BMkacGV1Jo2kBa1iO%2BS594FEBfNiNzA%40mail.gmail.com.
For more options, visit https://groups.google.com/groups/opt_out.