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

Sorry...

Are there more columns on titles entity? If yes... the problem is that you tried to show columns you weren't grouping... and on postgresql it blows an exception... but if there is only one column on titles... (titles.id) so, I can't figure out what's the problem you mentioned...

What were your expected SQL to say that the result were malformed?

Atenciosamente,

Carlos Figueiredo

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

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/CANPOtXu-qYKESOxwvgTjM4%2BVxgfFr06x7hEFMHG5t9P8ub_n1A%40mail.gmail.com.
For more options, visit https://groups.google.com/groups/opt_out.