ActiveRecord::StatementInvalid error occurs only by invoking SQL query via resque-scheduler

Question

Why does the error occur only on executing via Resque scheduler?

I don't know why only username is selected on invoking via resque-scheduler. I expect that the all columns of users table are selected.

The job invoked by resque-scheduler is something like this:

class UserLicenseJob
  @queue = :some_queue

  def self.perform(*args)
    users = User.license_count_exceeded
    # Do something to the 'users'
  end
end

The error log is show as below:

failed: #<ActiveRecord::StatementInvalid: Mysql2::Error: Unknown column 'users.license_count' in 'having clause': SELECT `users`.`username` FROM `users` LEFT OUTER JOIN devices ON devices.user_id = users.id GROUP BY users.id HAVING users.license_count != 0 AND users.license_count > COUNT(devices.id)>

The relation between User and Device model is shown as below:

class User < ActiveRecord::Base

  has_many :devices

  scope :license_count_exceeded, -> {
    select("users.*, COUNT(devices.id) AS devices_count")
      .joins("LEFT OUTER JOIN devices ON devices.user_id = users.id")
      .group("users.id")
      .having("users.license_count != 0 AND users.license_count > COUNT(devices.id)")
  }

end

class Device < ActiveRecord::Base

  belongs_to :user

end

Successfully done by invoking via the Rails console.

irb(main):014:0> User.select("users.*, COUNT(devices.id) AS devices_count").joins("LEFT OUTER JOIN devices ON devices.user_id = users.id").group("users.id").having("license_count != 0 AND license_count > COUNT(devices.id)")
  User Load (0.5ms)  SELECT users.*, COUNT(devices.id) AS devices_count FROM `users` LEFT OUTER JOIN devices ON devices.user_id = users.id GROUP BY users.id HAVING license_count != 0 AND license_count > COUNT(devices.id)
=> #<ActiveRecord::Relation []>

Successfully done by invoking via the MySQL console.

mysql> SELECT users.*, COUNT(devices.id) AS devices_count FROM `users` LEFT OUTER JOIN devices ON devices.user_id = users.id GROUP BY users.id HAVING users.license_count != 0 AND users.license_count > COUNT(devices.id);
Empty set (0.00 sec)

Depending versions;

• ruby: 2.1.2p95
• MySQL: 5.6.20
• Ruby on Rails: 4.1.4
• mysql2: 0.3.17

Answer 1

Resolved. It's just a invlid SQL statement.

scope :license_count_exceeded, -> {
  select("users.license_count AS license_count, COUNT(devices.id) AS devices_count")
    .joins("LEFT OUTER JOIN devices ON devices.user_id = users.id")
    .where("users.license_count != 0")
    .group("users.license_count")
    .having("users.license_count > COUNT(devices.id)")
}