Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

Already on GitHub? Sign in to your account

Arel: #count does not work after #select("... AS ...") #2541

Closed
alexeymuranov opened this Issue Aug 16, 2011 · 10 comments

Comments

Projects
None yet
6 participants
Contributor

alexeymuranov commented Aug 16, 2011

Here are steps to reproduce the problem.

  • Create an application with 1 model:
rails new arel_test_app
cd arel_test_app/
rails generate model Person first_name:string last_name:string
rake db:migrate
  • In console:
Person.new(:first_name => 'J', :last_name => 'D').save
pp = Person.select("(first_name || ' ' || last_name) AS full_name")
pp.first.full_name  # => "J D"
pp.all.size  # => 1
pp.count  # SQLite3::SQLException

gives:

SELECT COUNT((first_name || ' ' || last_name) AS full_name) FROM "people" 
SQLite3::SQLException: near "AS": syntax error: SELECT COUNT((first_name || ' ' || last_name) AS full_name) FROM "people"

There is another probably related problem with joins, �select("... AS ..."), where, and count, so i'll show it here too.

  • Add second model and set up an association between the two:
rails generate model Member person_id:integer
rake db:migrate
class Person < ActiveRecord::Base
  has_one :member
end

class Member < ActiveRecord::Base
  belongs_to :person
end
  • In console:
Member.new(:person_id => 1).save
mm = Member.joins(:person).select("members.*, people.*, (first_name || ' ' || last_name) AS full_name").where("full_name LIKE 'J%'")
mm.first.full_name  # => "J D"
mm.all.size  # => 1
mm.count  # SQLite3::SQLException

gives:

SELECT COUNT(*) FROM "members" INNER JOIN "people" ON "people"."id" = "members"."person_id" WHERE (full_name LIKE 'J%')
SQLite3::SQLException: no such column: full_name: SELECT COUNT(*) FROM "members" INNER JOIN "people" ON "people"."id" = "members"."person_id" WHERE (full_name LIKE 'J%')

It is a serious problem in my opinion, as it also breaks kaminari when custom attributes have been added with "SELECT ... AS ...".

This is with rails 3.1.0rc5.

Member

jonleighton commented Aug 16, 2011

Is this is a regression?

Contributor

alexeymuranov commented Aug 16, 2011

No, the result is identical with rails 3.0.9.

Contributor

alexeymuranov commented Aug 20, 2011

It seems that in SQL one is supposed to write

SELECT COUNT(*) FROM people WHERE (first_name || ' ' || last_name) LIKE 'J%'

I was just hoping to be able to use virtual attributes the same way as column names.
Would be nice to be able to write where("full_name LIKE 'J%'")

Contributor

alexeymuranov commented Aug 23, 2011

I think i've found a better option for SQL (but i am not a specialist):

SELECT COUNT(*) FROM (SELECT (first_name || ' ' || last_name) AS full_name FROM people WHERE full_name LIKE 'J%')

Seems more logical to me but i was told that SELECT from SELECT is not supported by all databases.
I plan to look into possibility of using views too.

bborn commented Oct 17, 2011

+1. I think I'm seeing the same thing. Let's say you want to group your records by a column and order them by count:

models = Model.select("models.*, COUNT(*) as count").group("models.id")

As soon as you do models.count (or models.any? or any method that relies on count), you get problems, because the count method overrides your select method. The generated SQL will include select COUNT(*) as count_all instead of what you asked for, which would be select COUNT(*) as count.

bborn commented Oct 17, 2011

Should note that there's an easy work around; just declare your count with more specificity:

models = Model.select("models.*, COUNT('models'.id) as count").group("models.id")

bidaian commented Nov 23, 2011

bborn, I run into this very same problem (rails 3.0.9) and I don't see how the work around would work for the original problem:

pp = Person.select("(first_name || ' ' || last_name) AS full_name")

@bborn is this still a problem with 3.2.3?

Contributor

alexeymuranov commented May 5, 2012

@twinturbo, this is a problem with 3.2.3 alright. Here is a script to reproduce:

gem 'activerecord', '3.2.3'
require 'active_record'
require 'logger'

# Print out what version we're running
puts "Active Record #{ActiveRecord::VERSION::STRING}"

ActiveRecord::Base.logger = Logger.new(STDOUT)

# Connect to an in-memory sqlite3 database
ActiveRecord::Base.establish_connection( :adapter  => 'sqlite3',
                                         :database => ':memory:' )

# Create the minimal database schema necessary to reproduce the bug
ActiveRecord::Schema.define do
  create_table "people", :force => true do |t|
    t.string :first_name
    t.string :last_name
  end
end

# Create the minimal set of models to reproduce the bug
class Person < ActiveRecord::Base
end

# Working code:
Person.new(:first_name => 'J', :last_name => 'D').save
people = Person.select("(first_name || ' ' || last_name) AS full_name")
people.first.full_name  # => "J D"
people.all.size  # => 1

# THE BUG:
people.count
#    (0.2ms)  SELECT COUNT((first_name || ' ' || last_name) AS full_name) FROM "people"
# SQLite3::SQLException: near "AS": syntax error: SELECT COUNT((first_name || ' ' || last_name) AS full_name) FROM "people"
# ActiveRecord::StatementInvalid: SQLite3::SQLException: near "AS": syntax error: SELECT COUNT((first_name || ' ' || last_name) AS full_name) FROM "people"
Member

senny commented Feb 21, 2013

This ticket is very old and I like to get is resolved. I must say I don't get the described problem though. ActiveRecord generates the query correctly but it's not valid SQL. You can't use AS inside a count expression. This will fail for sqlite, mysql and postgres. Since you add the AS statement yourself, there is no way ActiveRecord could generate a valid query.

It works if you remove the AS clause like:

Person.select("(first_name || ' ' || last_name)").count

I'm closing this for now. Please reopen if I misunderstood your problem.

@senny senny closed this Feb 21, 2013

@d2bit d2bit added a commit to Soluciones/kpi that referenced this issue Dec 22, 2014

@d2bit d2bit Change .count to .size
* count doesn't work with select (... as ...)
rails/rails#2541
2204c84
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment