我对COUNT('e.id')或COUNT(e.id)的查询有不同的结果 [英] I have different results from query for COUNT('e.id') or COUNT(e.id)

问题描述

我有以下代码:

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")

我的测试成功完成，没有错误.当我使用

my test completes without errors. When I use

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

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

my test fails. I can't understand why. Can u explain why? I use Rails-4 and 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

推荐答案

查询COUNT('e.id')或COUNT(e.id)的结果不同

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

'e.id'是一个字符串常量，所以COUNT('e.id')只是一个笨拙，令人误解的说法COUNT(*).

'e.id' is a string constant, so COUNT('e.id') is just an awkward, misleading way of saying COUNT(*).

COUNT(e.id)会对结果中的所有行进行计数，其中e.id IS NOT NULL-因为count()不对NULL值进行计数.

COUNT(e.id), on the other hand, counts all rows in the result where e.id IS NOT NULL - since count() does not count NULL values.

关于count()的手册:

count(*) ...输入行数

count(*) ... number of input rows

count(expression) ...要为其输入的输入行数 表达式的值不为空

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

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

As you can see, there are even two separate functions internally. And it should be noted that count(*) is slightly faster. So use that unless you need the second variant. Related:

• PostgreSQL:运行计数查询的行(按分钟)

您可能会反驳:
但是e.id是core_employments的PRIMARY KEY，因此它被定义为NOT NULL！"

You might counter with:
"But e.id is the PRIMARY KEY of core_employments, so it is defined NOT NULL!"

但是，这将忽略查询中的条件LEFT JOIN ，该条件仍在NOT NULL列中引入了NULL值，其中不满足连接条件.相关:

But that would overlook the conditional LEFT JOIN in your query that still introduces NULL values in your NOT NULL column, where the join conditions are not met. Related:

• 使用LEFT查询JOIN不返回计数为0的行

也就是说，LEFT [OUTER] JOIN也具有误导性.以后的条件

That said, LEFT [OUTER] JOIN is misleading, too. The later condition

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

强制它像普通的[INNER] JOIN一样工作.修复该问题后，您还可以简化为:

forces it to act like a plain [INNER] JOIN. Once you have fixed that, you might as well simplify to:

having("COUNT(*) = 1")

如果您只关心 至少 ，则core_employments中存在一个相关的行，从而转化为having("COUNT(*) >= 1")，是一个简单的案例就是 EXISTS半加入:

And if all you care is that at least one related row exists in core_employments, translating to having("COUNT(*) >= 1"), the superior (clearer, faster) technique in simple cases would be an EXISTS semi-join:

WHERE EXISTS (SELECT FROM core_employments WHERE <conditions>)

这篇关于我对COUNT('e.id')或COUNT(e.id)的查询有不同的结果的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！