mysql - Rails 范围 null 返回错误结果

Question

我想查找 work_end_at IS NULL 的记录。

work_end_at的类型是datetime。而且，我尝试了 WorkingHistory.where("work_end_at IS NULL")。

我得到了一个空结果。然而，id 为 532 的记录正是我想要查找的记录。

ruby 版本:2.1.5

rails版本:4.0.4

rails console result

WorkingHistory.where("work_end_at IS NULL")
SELECT `working_histories`.* FROM `working_histories` WHERE (work_end_at IS NULL)
[]

编辑:

console result

# console result
WorkingHistory.where(work_end_at: [nil, ""])
SELECT `working_histories`.* FROM `working_histories` WHERE ((`working_histories`.`work_end_at` = '' OR `working_histories`.`work_end_at` IS NULL))
[]

WorkingHistory.where(work_end_at: nil)
SELECT `working_histories`.* FROM `working_histories` WHERE `working_histories`.`work_end_at` IS NULL
[]

#WorkingHistory.find(532)
SELECT `working_histories`.* FROM `working_histories` WHERE `working_histories`.`id` = 532 LIMIT 1
#<WorkingHistory:0x0055d8737a8838> {
                       :id => 532,
                  :user_id => 63,
              :contract_id => 220,
            :work_start_at => Thu, 05 Jul 2018 13:54:28 CST +08:00,
               :created_at => Thu, 05 Jul 2018 13:54:33 CST +08:00,
               :updated_at => Thu, 16 Aug 2018 11:13:21 CST +08:00,
              :work_end_at => nil,
            :work_time_sec => 3721,
    :contract_milestone_id => 822
}

# WorkingHistory model
class WorkingHistory < ActiveRecord::Base
  belongs_to :user
  belongs_to :contract
  belongs_to :contract_milestone

  has_one :memo
  has_many :snapshots
  has_many :details, class_name: 'WorkingHistoryDetail'

  scope :custom_region, -> (date) { where(work_start_at: date) }
  scope :custom_day, -> (date) { where(work_start_at: date.beginning_of_day..date.end_of_day) }
  scope :custom_week, -> (date) { where(work_start_at: date.beginning_of_week..date.end_of_week) }
  scope :custom_month, -> (date) { where(work_start_at: date.beginning_of_month..date.end_of_month) }
  scope :custom_year, -> (date) { where(work_start_at: date.beginning_of_year..date.end_of_year) }
  scope :relate, -> (datetime) { where('work_start_at < ? && work_end_at >= ?', datetime, datetime) }
  scope :current, -> { where('work_start_at < ? && work_end_at = ?', Time.zone.now, '0000-00-00 00:00:00') }
  scope :cross_cycle_working, -> { where('dayofweek(work_start_at) = ? AND dayofweek(work_end_at) = ?', 6, 1) }
  scope :not_end_or_end_at_today, -> { where('work_end_at >= ?', Time.zone.today.beginning_of_day) }
  scope :without_end_at, -> { where("work_end_at IS NULL or work_end_at = ''") }

  before_save :assign_to_contract_milestone

  def end_working?
    self.work_end_at.present?
  end

  def update_work_time_sec
    return unless self.end_working?

    limit_hours = contract.limit_hours * 3600
    total_working_time = contract_milestone.working_histories.where("working_histories.id != ?", self.id).sum(:work_time_sec)
    details_sum = self.details.summation
    if (details_sum + total_working_time) > limit_hours
      difference = (limit_hours - total_working_time) > 0 ? (limit_hours - total_working_time) : 0
      self.update!(work_time_sec: difference)
    else
      secs = details_sum > 0 ? details_sum : 0
      self.update!(work_time_sec: secs)
    end
  end

  private

  def work_end_and_yet_assign?
    work_time_sec.present? && contract_milestone_id.blank?
  end
end


# db schema

  create_table "working_histories", force: true do |t|
    t.integer  "user_id"
    t.integer  "contract_id"
    t.datetime "work_start_at"
    t.datetime "created_at"
    t.datetime "updated_at"
    t.datetime "work_end_at"
    t.integer  "work_time_sec",         default: 0,     null: false
    t.integer  "contract_milestone_id"
    t.boolean  "finish",                default: false
  end

编辑:

#The result of querying the data directly with MySQL

mysql> SELECT * FROM working_histories where `working_histories`.`id` = 532;
+-----+---------+-------------+---------------------+---------------------+---------------------+---------------------+---------------+-----------------------+--------+
| id  | user_id | contract_id | work_start_at       | created_at          | updated_at          | work_end_at         | work_time_sec | contract_milestone_id | finish |
+-----+---------+-------------+---------------------+---------------------+---------------------+---------------------+---------------+-----------------------+--------+
| 532 |      63 |         220 | 2018-07-05 05:54:28 | 2018-07-05 05:54:33 | 2018-08-16 03:13:21 | 0000-00-00 00:00:00 |          3721 |                   822 |      1 |
+-----+---------+-------------+---------------------+---------------------+---------------------+---------------------+---------------+-----------------------+--------+
1 row in set (0.01 sec)

Answer 1

升级MySQL版本后问题仍未解决。

我发现MySQL schema中work_end_at的默认值为0000-00-0000:00 00:00。它与db/schema.rb不同。

我认为这就是我无法获得正确结果的原因。

mysql> describe working_histories;
+-----------------------+------------+------+-----+---------------------+----------------+
| Field                 | Type       | Null | Key | Default             | Extra          |
+-----------------------+------------+------+-----+---------------------+----------------+
| id                    | int(11)    | NO   | PRI | NULL                | auto_increment |
| user_id               | int(11)    | YES  | MUL | NULL                |                |
| contract_id           | int(11)    | YES  | MUL | NULL                |                |
| work_start_at         | datetime   | YES  |     | NULL                |                |
| created_at            | datetime   | YES  |     | NULL                |                |
| updated_at            | datetime   | YES  |     | NULL                |                |
| work_end_at           | datetime   | YES  |     | 0000-00-00 00:00:00 |                |
| work_time_sec         | int(11)    | NO   |     | 0                   |                |
| contract_milestone_id | int(11)    | YES  | MUL | NULL                |                |
| finish                | tinyint(1) | YES  |     | 0                   |                |
+-----------------------+------------+------+-----+---------------------+----------------+
10 rows in set (0.00 sec)