Rails:在HABTM关系中查找未连接的行 [英] Rails: Find rows without connection in HABTM relation

问题描述

我有两个与HABTM关系连接的模型Users和Leads:

I have two models, Users and Leads connected with HABTM relation:

class Lead < ActiveRecord::Base
  has_and_belongs_to_many :users
end

class User < ActiveRecord::Base
  has_and_belongs_to_many :leads
end

我现在如何只获得那些未与用户连接的销售线索?

How can I now get only those Leads which are not connected to Users?

提前谢谢！

推荐答案

您正在寻找的被称为

What you're looking for is known as an anti join .

有三种完成此操作的标准方法，

There are three standard ways to accomplish this,

1. 使用空的左外部联接
2. 在带有NOT&的子查询中使用where子句IN关键字
3. 在NOT&中使用where子句. EXISTS关键字

1. Using a null left outer join
2. Using a where clause with a sub query with the NOT & IN keywords
3. Using a where clause with the NOT & EXISTS keywords

基本上，EXISTS关键字将检查子查询是否返回了任何行，并报告作为匹配项，NOT显然否定了真正的匹配项.

Basically, the EXISTS keyword will check if any row is returned by the sub query and report that as a match, NOT obviously negates that true match.

这是我的首选方式(使用NOT& EXISTS)

here's my preferred way (using NOT & EXISTS)

class User < ActiveRecord::Base
  has_and_belongs_to_many :leads
  def self.without_leads
    where(<<-SQL)
      NOT EXISTS (SELECT 1 
        FROM   leads_users 
        WHERE  users.id = leads_users.user_id) 
    SQL
  end
end

class Lead < ActiveRecord::Base
  has_and_belongs_to_many :users
  def self.without_users
    where(<<-SQL)
      NOT EXISTS (SELECT 1 
        FROM   leads_users 
        WHERE  leads.id = leads_users.lead_id) 
    SQL
  end

 def self.not_connected_to(user)
    where(<<-SQL, user.id)
      NOT EXISTS (SELECT 1 
        FROM   leads_users 
        WHERE  leads.id = leads_users.lead_id
        AND leads_users.user_id = ?
        ) 
    SQL
  end
end

这是使用Arel的非SQL 方法

here's a non SQL approach using arel

class User < ActiveRecord::Base
  has_and_belongs_to_many :leads
  def self.without_leads
    habtm_table = Arel::Table.new(:leads_users)
    join_table_with_condition = habtm_table.project(habtm_table[:user_id])
    where(User.arel_table[:id].not_in(join_table_with_condition))
  end
end

class Lead < ActiveRecord::Base
  has_and_belongs_to_many :users
  def self.without_users
    habtm_table = Arel::Table.new(:leads_users)
    join_table_with_condition = habtm_table.project(habtm_table[:lead_id])
    where(Lead.arel_table[:id].not_in(join_table_with_condition))
  end
end

此处是示例存储库

查找没有潜在客户的用户

Find users without leads

User.where(user_id: 1).without_leads

在没有用户的情况下找到潜在客户

Find leads without users

Lead.without_users

查找未连接到特定用户的线索

Find leads not connected to a specific user

Lead.not_connected_to(user)

链接排序

Lead.without_users.order(:created_at)

这篇关于Rails:在HABTM关系中查找未连接的行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！