Relation count returns syntax error in Rails 4.1.X

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?

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

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

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 L. [email protected]

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

[email protected] 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 L. [email protected]

That was the point I was making in the issue I opened, yes.

On 1 June 2014 15:51, Rodrigo L. [email protected] 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

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

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

This forum is not affiliated to the Ruby language, Ruby on Rails framework, nor any Ruby applications discussed here.

| Privacy Policy | Terms of Service | Remote Ruby Jobs