postgresql - 我从查询 COUNT ('e.id' ) 或 COUNT(e.id) 得到不同的结果

Question

我有以下代码:

def self.department_members(department)
  where(organization_id: department.organization_id)
    .joins("LEFT JOIN core_employments As e ON
      e.organization_id = #{department.organization_id} AND
      core_members.user_id = e.user_id")
    .group('core_members.id')
end

def self.can_automerged(department)
  department_members(department).having("COUNT('e.id') = 1")
  # department_members(department).having("COUNT(e.id) = 1")
end

def self.can_not_automerged(department)
  department_members(department).having("Count('e.id') > 1")
end

当我使用

department_members(department).having("COUNT('e.id') = 1")

我的测试完成且没有错误。当我使用

department_members(department).having("COUNT(e.id) = 1")

我的测试失败了。我不明白为什么。你能解释为什么吗？我使用 Rails-4 和 PostgreSQL。

架构:

  create_table "core_members", force: :cascade do |t|
    t.integer  "user_id",                                    null: false
    t.integer  "project_id",                                 null: false
    t.boolean  "owner",                      default: false
    t.string   "login"
    t.string   "project_access_state"
    t.datetime "created_at"
    t.datetime "updated_at"
    t.integer  "organization_id"
    t.integer  "organization_department_id"
  end

  create_table "core_employments", force: :cascade do |t|
    t.integer  "user_id"
    t.integer  "organization_id"
    t.boolean  "primary"
    t.string   "state"
    t.datetime "created_at"
    t.datetime "updated_at"
    t.integer  "organization_department_id"
  end

测试:

module Core
  require "initial_create_helper"
  describe Member do
    describe "automerge" do
      before(:each) do
        @organization = create(:organization)
        @department1 = create(:organization_department,organization: @organization)
        @department2 = create(:organization_department,organization: @organization)

        @user = create(:user)
        @user_with_many_employments = create(:user)

        @department1.employments.create!(user: @user)
        @department1.employments.create!(organization: @organization, user: @user_with_many_employments)
        @department2.employments.create!(organization: @organization, user: @user_with_many_employments)
        @project = create_project
        @project.members.create!(user: @user,
                                organization: @organization)
        @project.members.create!(user: @user_with_many_employments,
                                organization: @organization)

      end

      it "::can_not_automerged" do
        expect(Member.can_not_automerged(@department1).to_a.map(&:user)).to match_array [@user_with_many_employments]
      end
      it "::can_automerged" do
        expect(Member.can_automerged(@department1).to_a.map(&:user)).to match_array [@user]
      end
    end
  end
end

Answer 1

I have different results from query for COUNT('e.id') or COUNT(e.id)

'e.id' 是一个字符串常量，因此 COUNT('e.id') 只是一种笨拙的误导性说法 COUNT( *).

另一方面，

COUNT(e.id) 计算结果中 e.id IS NOT NULL 的所有行 - 因为 count() 不计算 NULL 值。

The manual about count():

count(*) ... number of input rows

count(expression) ... number of input rows for which the value of expression is not null

如您所见，内部甚至有两个独立的功能。和应该注意 count(*) 稍微快一些。因此，除非您需要第二个变体，否则请使用它。相关:

• PostgreSQL: running count of rows for a query 'by minute'

你可以反击:
“但是e.id 是core_employments 的PRIMARY KEY，所以它被定义为NOT NULL! "

但这会忽略查询中的条件 LEFT JOIN，它仍然会在 NOT NULL 中引入 NULL 值> 列，不满足连接条件。相关:

• Query with LEFT JOIN not returning rows for count of 0

也就是说，LEFT [OUTER] JOIN 也具有误导性。后面的情况

having("COUNT(e.id) = 1")

强制它像一个普通的 [INNER] JOIN 一样工作。一旦你解决了这个问题，你不妨简化为:

having("COUNT(*) = 1")

如果您只关心至少 core_employments 中存在一个相关行，转换为having("COUNT( *) >= 1")，简单情况下的高级(更清晰、更快)技术将是 EXISTS semi-join :

WHERE EXISTS (SELECT FROM core_employments WHERE <conditions>)