Forum: Ruby on Rails Using :group with #count generating bad SQL in Postgres

1df75184e7ea8e155271683d98d2d67f?d=identicon&s=25 Yaw B. (yaw_b)
on 2013-07-22 23:23
(Received via mailing list)
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
06e4c84000579d88c9298a6616a42e29?d=identicon&s=25 Carlos Figueiredo (Guest)
on 2013-07-23 17:01
(Received via mailing list)
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:
1df75184e7ea8e155271683d98d2d67f?d=identicon&s=25 Yaw B. (yaw_b)
on 2013-07-23 18:50
(Received via mailing list)
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:

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



--
visit my blog @ yawboakye.blogspot.com     | call me on +233242057831 or
+233273201210
06e4c84000579d88c9298a6616a42e29?d=identicon&s=25 Carlos Figueiredo (Guest)
on 2013-07-23 21:13
(Received via mailing list)
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 <http://titles.id> ORDER BY comments_count
DESC

Atenciosamente,

*Carlos Figueiredo*
06e4c84000579d88c9298a6616a42e29?d=identicon&s=25 Carlos Figueiredo (Guest)
on 2013-07-23 21:19
(Received via mailing list)
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 <
1df75184e7ea8e155271683d98d2d67f?d=identicon&s=25 Yaw B. (yaw_b)
on 2013-07-24 02:29
(Received via mailing list)
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.figueiredo87@gmail.com> wrote:

> *Carlos Figueiredo*
>>     joins(:comments).
>> titles.name, titles.whatever <http://titles.id> ORDER BY comments_count
>>> Can you write the right (no pun intended) query here? Thanks
>>>>
>>>>>     group('titles.id').
>>>>> I've contacted @steveklabnik and he'd re-open an issue reporting this
>>>>> To post to this group, send email to rubyonrails-talk@googlegroups.com
>>>> Groups "Ruby on Rails: Talk" group.
>>>>
>>> To unsubscribe from this group and stop receiving emails from it, send
>>
>
> For more options, visit https://groups.google.com/groups/opt_out.
>
>
>



--
visit my blog @ yawboakye.blogspot.com     | call me on +233242057831 or
+233273201210
15eb13d662803f57bc4aea59704988b4?d=identicon&s=25 Scott Ribe (Guest)
on 2013-07-24 02:37
(Received via mailing list)
On Jul 23, 2013, at 6:28 PM, Yaw Boakye elGran <yawboakye10@gmail.com>
wrote:

>    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

You seem to be confusing the grouping with the join condition. The join
requires 1 (usually, sometimes more) column common to both tables. The
grouping must include all columns not used in the aggregate function.


--
Scott Ribe
scott_ribe@elevated-dev.com
http://www.elevated-dev.com/
(303) 722-0567 voice
1df75184e7ea8e155271683d98d2d67f?d=identicon&s=25 Yaw B. (yaw_b)
on 2013-07-24 12:34
(Received via mailing list)
I think so, sorry for the misinformation. Thanks for the correction :)




On Wed, Jul 24, 2013 at 12:36 AM, Scott Ribe
<scott_ribe@elevated-dev.com>wrote:

> grouping must include all columns not used in the aggregate function.
>
>
>
>


--
visit my blog @ yawboakye.blogspot.com     | call me on +233242057831 or
+233273201210
1df75184e7ea8e155271683d98d2d67f?d=identicon&s=25 Yaw B. (yaw_b)
on 2013-07-24 12:40
(Received via mailing list)
@Carlos this query still throws an exception:

def self.order_by_number_of_comments_descending
        select('titles.id, titles.title, titles.submitter_name,
titles.submitter_email, titles.created_at, titles.updated_at, count(
comments.id) AS comments_count').
        joins(:comments).
        group('titles.id, titles.title, titles.submitter_name,
titles.submitter_email, titles.created_at, titles.updated_at').
        order('comments_count DESC')
    end



still complaining about not finding comments_count :/

I'm using *PostgreSQL 9.1.5*


On Wed, Jul 24, 2013 at 10:32 AM, Yaw Boakye elGran
<yawboakye10@gmail.com>wrote:

>> >        During grouping, the normal SQL requirement is to group on a
>> Scott Ribe
>> To unsubscribe from this group and stop receiving emails from it, send an
>
> --
> visit my blog @ yawboakye.blogspot.com     | call me on +233242057831 or
> +233273201210
>



--
visit my blog @ yawboakye.blogspot.com     | call me on +233242057831 or
+233273201210
1df75184e7ea8e155271683d98d2d67f?d=identicon&s=25 Yaw B. (yaw_b)
on 2013-07-24 12:41
(Received via mailing list)
Error message:

PG::Error: ERROR:  column "comments_count" does not exist
LINE 1: ...l, titles.created_at, titles.updated_at  ORDER BY
comments_c...
                                                             ^
: SELECT COUNT(*) AS count_all, titles.id, titles.title,
titles.submitter_name, titles.submitter_email, titles.created_at,
titles.updated_at AS
titles_id_titles_title_titles_submitter_name_titles_submitter_e FROM
"titles" INNER JOIN "comments" ON "comments"."title_id" =
"titles"."id" GROUP BY titles.id, titles.title, titles.submitter_name,
titles.submitter_email, titles.created_at, titles.updated_at  ORDER BY
comments_count DESC




On Wed, Jul 24, 2013 at 10:38 AM, Yaw Boakye elGran
<yawboakye10@gmail.com>wrote:

>     end
>
>>>
>>> --
>>> Groups "Ruby on Rails: Talk" group.
>>
> +233273201210
>



--
visit my blog @ yawboakye.blogspot.com     | call me on +233242057831 or
+233273201210
06e4c84000579d88c9298a6616a42e29?d=identicon&s=25 Carlos Figueiredo (Guest)
on 2013-07-24 13:12
(Received via mailing list)
Now I figured out what is the error...
I dont know why... But rails changed the column name where you count
froum
comments_count to count_all... And you refer it on your order by
statement
using the alias you choose...
I hope somone more experienced on rails help you fix it, but for now...
U
could do 2 fix...

You can call the alias rails choose (count_all) on your order statement
(ugly way to solve)
Or you can call for count(*) without alias on your order statement (more
practiced by dba... Less ugly to solve)
Em 24/07/2013 07:40, "Yaw Boakye elGran" <yawboakye10@gmail.com>
escreveu:
06e4c84000579d88c9298a6616a42e29?d=identicon&s=25 Carlos Figueiredo (Guest)
on 2013-07-24 13:20
(Received via mailing list)
@Yaw, can you test if you change the way you call the count on postgres,
rails still change the name of the column?

Ex. Try using count(comments.id) or count(1) besides count(*) on your
query
and let me know what happens...

Ps.: The ugly fix of my last email still works
Em 24/07/2013 08:10, "Carlos Figueiredo" <carlos.figueiredo87@gmail.com>
escreveu:
Please log in before posting. Registration is free and takes only a minute.
Existing account

NEW: Do you have a Google/GoogleMail, Yahoo or Facebook account? No registration required!
Log in with Google account | Log in with Yahoo account | Log in with Facebook account
No account? Register here.