Forum: Ruby on Rails Relation count returns syntax error in Rails 4.1.X

Fba85550f845ee03a40e5397d1164048?d=identicon&s=25 Rodrigo Lueneberg (rodusa)
on 2014-06-01 01:59
Until Rails 4.0.4 everything was working fine with the way Rails
implements  the COUNT method until I upgraded to latest Rails version.

It seems to be a bug because from the error message bellow looks  like
rails is doing just a simple count(orders_headers.id, ... )without using
the proper SELECT SQL syntax, for instance, such as this one:

select count(id) from (SELECT COUNT(orders_header.id,
orders_header.created_at) FROM `orders_header`  WHERE (shop_id=99 and
customer_id=1 and hash_key like
'539de64e8793790430052bc861dd0ff521334e32')

Ps: By the way, from the error message below you don't need to do a
count on multiple columns to get the result, you just need to do a count
on just one column.

query= OrderHeader.select("orders_header.id,
orders_header.created_at").where("shop_id=#{shop_id} and
customer_id=#{customer_id} and hash_key like
'#{current_hash_key}'").order("id desc")
       if query.nil?
         return true # no duplicates found
       end
       if (query.count>0) # duplicates found
          # I get the error righ here
       end

******** ERROR MESSAGE *****************************
SELECT COUNT(orders_header.id, orders_header.created_at) FROM
`orders_header`  WHERE (shop_id=99 and customer_id=1 and hash_key like
'539de64e8793790430052bc861dd0ff521334e32')

Mysql2::Error: You have an error in your SQL syntax; check the manual
that corresponds to your MySQL server version for the right syntax to
use near ' orders_header.created_at) FROM `orders_header`  WHERE
(shop_id=99 and customer_' at line 1: SELECT COUNT(orders_header.id,
orders_header.created_at) FROM `orders_header`  WHERE (shop_id=99 and
customer_id=1 and hash_key like
'539de64e8793790430052bc861dd0ff521334e32')
*********************** || *****************************

It works if I use SIZE but this is not good when the result is big. And
I do have other queries that are facing the same problem. The problem
apparently seems to be related to .select method, specifically related
to the columns specified in SELECT because when I use only
.where everything works fine.

Is there a workaround for this problem? Is this really a bug?
5b50fec1e5bf39bb4d75b199130882f7?d=identicon&s=25 Josh Jordan (Guest)
on 2014-06-01 14:04
(Received via mailing list)
Rails introduced a breaking change to select and count being used
together
in the same relation. Either remove your call to "select", since it is
unnecessary here, or call "count(:all)".

More info here: https://github.com/rails/rails/issues/15138
Fba85550f845ee03a40e5397d1164048?d=identicon&s=25 Rodrigo Lueneberg (rodusa)
on 2014-06-01 15:19
Josh,

What about select.count(1)? I haven't tried this option which may work
according to this link.
https://github.com/rails/rails/issues/13648

If I remove the call to "select" how to restrict which columns to
select?

PS: You don't want Rails to select all using wildcard * as it will
affect performance.

BEFORE
OrderHeader.select("orders_header.id,
orders_header.created_at").where("shop_id=#{shop_id} and
customer_id=#{customer_id} and hash_key like
'#{current_hash_key}'").order("id desc")

AFTER
OrderHeader.where("shop_id=#{shop_id} and
customer_id=#{customer_id} and hash_key like
'#{current_hash_key}'").order("id desc")

Thanks
Rod
5b50fec1e5bf39bb4d75b199130882f7?d=identicon&s=25 josh.jordan@gmail.com (Guest)
on 2014-06-01 15:48
(Received via mailing list)
If you're calling count, it doesn't matter what select values you've
passed, since you're asking ActiveRecord to return an aggregate, not any
column values.


On Sun, Jun 1, 2014 at 9:19 AM, Rodrigo Lueneberg <lists@ruby-forum.com>
Fba85550f845ee03a40e5397d1164048?d=identicon&s=25 Rodrigo Lueneberg (rodusa)
on 2014-06-01 16:51
I see that, but sometimes you want to reuse code in the same relation.
For instance if you do a query first using restricted columns and later
you need to do a count based on the result. Let's say in the first
relation with selected columns (col1, col2, etc.) you want to use it to
fill an array to write columns in a view.

From what I understand it doesn't work if I try to obtain
a count from this relation. In fact, it doesn't make sense to do a
relation just to do a count.

In this scenario you would need two relations, one for the selected
columns and another only for count. I would prefer do it using raw SQL,
don't you agree?

Rod

josh.jordan@gmail.com wrote in post #1148444:
> If you're calling count, it doesn't matter what select values you've
> passed, since you're asking ActiveRecord to return an aggregate, not any
> column values.
>
>
> On Sun, Jun 1, 2014 at 9:19 AM, Rodrigo Lueneberg <lists@ruby-forum.com>
5b50fec1e5bf39bb4d75b199130882f7?d=identicon&s=25 josh.jordan@gmail.com (Guest)
on 2014-06-01 16:56
(Received via mailing list)
That was the point I was making in the issue I opened, yes.
4c6bde00168d595053c09aac7e487f8e?d=identicon&s=25 Colin Law (Guest)
on 2014-06-01 17:00
(Received via mailing list)
On 1 June 2014 15:51, Rodrigo Lueneberg <lists@ruby-forum.com> wrote:
> don't you agree?
Why not just do the count on the array?  That would save running two
queries, one for the data and one for the count.  Or am I missing
something?

Colin
Fba85550f845ee03a40e5397d1164048?d=identicon&s=25 Rodrigo Lueneberg (rodusa)
on 2014-06-01 17:02
Thanks, I am glad we are on the same page. Sorry, I did not see your
github post. I will check that right now.
Rod
Fba85550f845ee03a40e5397d1164048?d=identicon&s=25 Rodrigo Lueneberg (rodusa)
on 2014-06-01 17:47
Colin,

Doing count on the array is expensive when you have thousands of rows
where you'd have to pull all those records in memory to do a count. It's
much more efficient to do it in db, in this case with large sets.

By the way what would be more efficient, count(:all) or count(1)?
thanks
Rod
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.