[sql] 연관 수가 0보다 큰 모든 레코드 찾기

Program / 글쓴이

단순 할 거라고 생각했지만 그렇지 않은 일을하려고합니다.

공석이 많은 프로젝트 모델이 있습니다.

class Project < ActiveRecord::Base

  has_many :vacancies, :dependent => :destroy

end

공석이 1 개 이상인 모든 프로젝트를 받고 싶습니다. 나는 다음과 같이 시도했다.

Project.joins(:vacancies).where('count(vacancies) > 0')

그러나 그것은 말한다

SQLite3::SQLException: no such column: vacancies: SELECT "projects".* FROM "projects" INNER JOIN "vacancies" ON "vacancies"."project_id" = "projects"."id" WHERE ("projects"."deleted_at" IS NULL) AND (count(vacancies) > 0).

답변

joins기본적으로 내부 조인을 사용하므로를 사용 Project.joins(:vacancies)하면 실제로 관련 공석이있는 프로젝트 만 반환됩니다.

최신 정보:

주석에서 @mackskatz가 지적했듯이 group절 없이 위 코드는 둘 이상의 공석이있는 프로젝트에 대해 중복 프로젝트를 반환합니다. 중복을 제거하려면

Project.joins(:vacancies).group('projects.id')

최신 정보:

@Tolsee가 지적했듯이 distinct.

Project.joins(:vacancies).distinct

예로서

[10] pry(main)> Comment.distinct.pluck :article_id
=> [43, 34, 45, 55, 17, 19, 1, 3, 4, 18, 44, 5, 13, 22, 16, 6, 53]
[11] pry(main)> _.size
=> 17
[12] pry(main)> Article.joins(:comments).size
=> 45
[13] pry(main)> Article.joins(:comments).distinct.size
=> 17
[14] pry(main)> Article.joins(:comments).distinct.to_sql
=> "SELECT DISTINCT \"articles\".* FROM \"articles\" INNER JOIN \"comments\" ON \"comments\".\"article_id\" = \"articles\".\"id\""

답변

1) 최소 1 개의 공석이있는 프로젝트를 얻으려면 :

Project.joins(:vacancies).group('projects.id')

2) 공석이 2 개 이상인 프로젝트를 얻으려면 :

Project.joins(:vacancies).group('projects.id').having('count(project_id) > 1')

3) 또는 Vacancy모델이 카운터 캐시를 설정하는 경우 :

belongs_to :project, counter_cache: true

그러면 이것도 작동합니다.

Project.where('vacancies_count > ?', 1)

에 대한 굴절 규칙을 수동으로 지정vacancy 해야 할 수 있습니까?

답변

예, vacancies조인의 필드가 아닙니다. 나는 당신이 원한다고 믿는다 :

Project.joins(:vacancies).group("projects.id").having("count(vacancies.id)>0")

답변

# None
Project.joins(:vacancies).group('projects.id').having('count(vacancies) = 0')
# Any
Project.joins(:vacancies).group('projects.id').having('count(vacancies) > 0')
# One
Project.joins(:vacancies).group('projects.id').having('count(vacancies) = 1')
# More than 1
Project.joins(:vacancies).group('projects.id').having('count(vacancies) > 1')

답변

groupor 와 결합 된 has_many 테이블에 대한 내부 조인을 수행하는 uniq것은 잠재적으로 매우 비효율적이며 SQL에서는 EXISTS상관 하위 쿼리와 함께 사용하는 세미 조인으로 더 잘 구현됩니다 .

이를 통해 쿼리 옵티마이 저는 올바른 project_id를 가진 행이 있는지 확인하기 위해 vacancies 테이블을 조사 할 수 있습니다. 해당 project_id가있는 행이 1 개인 지 백만 개인지는 중요하지 않습니다.

Rails에서 그렇게 간단하지는 않지만 다음을 통해 달성 할 수 있습니다.

Project.where(Vacancies.where("vacancies.project_id = projects.id").exists)

마찬가지로 공석이없는 모든 프로젝트를 찾습니다.

Project.where.not(Vacancies.where("vacancies.project_id = projects.id").exists)

편집 : 최근 Rails 버전에서는 existsarel에 위임되는 것에 의존하지 말라고 알려주는 폐기 경고가 표시 됩니다. 이 문제를 다음과 같이 수정하십시오.

Project.where.not(Vacancies.where("vacancies.project_id = projects.id").arel.exists)

편집 : 원시 SQL이 불편한 경우 다음을 시도하십시오.

Project.where.not(Vacancies.where(Vacancy.arel_table[:project_id].eq(Project.arel_table[:id])).arel.exists)

arel_table예를 들어 다음 과 같이의 사용을 숨기는 클래스 메서드를 추가하여이를 덜 지저분하게 만들 수 있습니다 .

class Project
  def self.id_column
    arel_table[:id]
  end
end

… 그래서 …

Project.where.not(
  Vacancies.where(
    Vacancy.project_id_column.eq(Project.id_column)
  ).arel.exists
)

답변

Rails 4+에서는 include 또는 eager_load 를 사용 하여 동일한 답을 얻을 수도 있습니다 .

Project.includes(:vacancies).references(:vacancies).
        where.not(vacancies: {id: nil})

Project.eager_load(:vacancies).where.not(vacancies: {id: nil})

답변

더 간단한 해결책이 있다고 생각합니다.

Project.joins(:vacancies).distinct