检查has_many关系中的所有记录是否与rails SQL查询中的条件匹配

Question

我正试图创建一个SQL查询，该查询基于has_many关系中记录的特定属性进行筛选。

我有程序测试，它有很多ivr_engagement，我想找到没有ivr_enginement.call_hook_number==2的ivr_engage的测试。本质上，call_hook_number==2的test.ivr_engagements的计数应该为零。

对象：

>> test.ivr_engagements
=> [#<IvrEngagement id: 281, user_id: 438431, testa_id: 508351, call_count: 1, call_hook_number: 1, qualified_response: true, ivr_application_id: 6741>, 
#<IvrEngagement id: 311, user_id: 438431, testa_id: 508351, call_count: 1, call_hook_number: 2, qualified_response: true, ivr_application_id: 6741>]

其中一个ivr_engagement的call_hook_number为2。我想筛选出任何与该标准匹配的ivr_engagement测试。

这个查询当前返回true(我希望它是false，因为有一个记录test.ivr_engagements的call_hook_number为2(：

>> program.tests.find(:all, :include => [:ivr_engagements], :conditions => "ivr_engagements.call_hook_number != 2").include? test
=> true
>> 

我还尝试过：

>> program.tests.find(:all, :include => [:ivr_engagements], :conditions => "NOT EXISTS(SELECT 1 FROM ivr_engagements WHERE ivr_engagements.call_hook_number = 2)").include? test
=> false

我认为这是有效的，但当我删除ivr_engagement:问题时，它应该是真的

>> test.ivr_engagements
=> [#<IvrEngagement id: 281, user_id: 438431, testa_id: 508351, call_count: 1, call_hook_number: 1, qualified_response: true, ivr_application_id: 6741>, 
#<IvrEngagement id: 311, user_id: 438431, testa_id: 508351, call_count: 1, call_hook_number: 2, qualified_response: true, ivr_application_id: 6741>]
>> test.ivr_engagements.last.destroy
=> #<IvrEngagement id: 311, user_id: 438431, testa_id: 508351, call_count: 1, call_hook_number: 2, qualified_response: true, ivr_application_id: 6741>
>> program.reload.tests.find(:all, :include => [:ivr_engagements], :conditions => "NOT EXISTS(SELECT 1 FROM ivr_engagements WHERE ivr_engagements.call_hook_number = 2)").include? test
=> false

也尝试过：

>> program.tests.find(:all, :include => [:ivr_engagements], :conditions => "(SELECT count(*) FROM ivr_engagements WHERE ivr_engagements.call_hook_number = 2)=0")

但这也没有奏效。

希望找到正确的查询。提前感谢！

Answer 1

在这种情况下，您可以使用Join，就像在您的示例中一样，您已经有了程序，那么我们可以继续使用它。

program.tests.joins(:ivr_engagements).where(:ivr_engagements => {call_hook_number: 2})

我们发现具有ivr约定的测试具有值为2的callbooknumber。您也可以在同一查询中发送测试的条件。

作为旁注，如果您以后有其他情况，joins部分需要像关系一样，单数或复数，但第二个，关于where，需要复数，因为它是表的名称。(在这种情况下是一样的，但如果你正在寻找一个属于，这很重要(

Answer 2

获取程序的test_ids

test_ids = program.tests.pluck(:id)

现在根据您的情况获取IvrEngagement。

IvrEngagement.where("testa_id IN (?) AND call_hook_number = ?", test_ids, 2)

或者，您也可以尝试：=>

Test.includes(:ivr_engagements).where("ivr_engagements.call_hook_number = ?", 2).distinct