Relation count returns syntax error in Rails 4.1.X

Rails
1

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?

2

Josh,

What about select.count(1)? I haven’t tried this option which may work
according to this link.

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

3

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: Invalid SQL generated when using count with select values · Issue #15138 · rails/rails · GitHub

4

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]

5

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]

6

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

7

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

8

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

9

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