There are at least 2 problems with how you recommend the query to be
written:
1. Why would titles.* work in regular SQL but not in Rails'
selectmethod? What would be the case if I have 100 columns on the
titles table?
2. During grouping, the normal SQL requirement is to group on a column
that is on both tables. As far as I know, grouping can't be done on two
tables when they don't a common column. In your group call, only
titles.id would be useful. The rest won't
Did you try running the resulting query? It should throw an exception
concerning the GROUP BY
On Tue, Jul 23, 2013 at 7:18 PM, Carlos Figueiredo <
carlos.figueired...@gmail.com> wrote:
> 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.figueired...@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.nameas
>> 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 <http://titles.id> ORDER BY comments_count
>> DESC
>>
>> Atenciosamente,
>>
>> *Carlos Figueiredo*
>>
>>
>> On Tue, Jul 23, 2013 at 1:48 PM, Yaw Boakye elGran <yawboaky...@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.figueired...@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" <yawboaky...@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. <https://github.com/rails/rails/issues/5588> 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+unsubscr...@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+unsubscr...@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+unsubscr...@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+unsubscr...@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.
>
>
>
--
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+unsubscr...@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/CAKwoQDut5NDvPiQc6Q7rC%2BU%3DjnDOpTSW2hEB2pQ3ezVQTSEfTQ%40mail.gmail.com.
For more options, visit https://groups.google.com/groups/opt_out.
