Until Rails 4.0.4 everything was working fine until I upgraded to latest
Rails version. It seems to be a bug because from the error message looks
like rails is doing just a count(Bla bla )without using properly using a
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')
Is there a workaround for this problem?
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
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')
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)”.
If I remove the call to "select" how to specify which columns to select?
PS: You don't want Rails to select all using wildcard * as it will
affect
performance.
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.
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 to
fill an array. 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?
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 to
fill an array. 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?
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?