查找不存在关联记录的父记录 [英] Find parent records where associated records are not present

问题描述

这是我所拥有的关联。

award_test.rb

has_many :instructor_student_awards , :dependent => :destroy
has_many :award_test_payment_notifications, dependent: :destroy

instructor_student.rb

has_many :instructor_student_awards, :dependent => :destroy
has_many :awards, through: :instructor_student_awards
has_many :award_test_payment_notifications, dependent: :destroy

instructor_student_award.rb

belongs_to :award
belongs_to :instructor_student
belongs_to :award_test

award_test_payment_notification.rb

belongs_to :award_test
belongs_to :instructor_student

schema.rb

"award_tests"
    t.date     "test_date"
    t.time     "test_time"
    t.integer  "award_id"
    t.decimal  "test_fee"


"award_test_payment_notifications"
    t.text     "response_params"
    t.string   "status"
    t.string   "transaction_id"
    t.string   "paid_by"
    t.float    "amount"
    t.string   "merchant_reference"
    t.integer  "award_test_id"
    t.integer  "instructor_student_id"

 "instructor_student_awards"
    t.integer  "instructor_student_id"
    t.integer  "award_test_id"
    t.boolean  "is_registered",         default: false

任何指导学生都可以注册奖励测试。一名讲师学生可以注册参加多个奖励测试。
的指导学生必须支付在奖励测试中进行测试的费用。指导学生可以两种方式支付奖励测试费用。

Any instructor student can register in award tests. one instructor student can register in multiple award tests. instructor student have to pay fee for giving test in award test. instructor student can pay in 2 ways for award test.

1. 在线

1. online

现金

导师学生为该奖项考试付费。比导师学生和award_test数据将被插入 award_test_payment_notifications 中。 award_test_notifications表包含与学生考试和奖励测试有关的所有字段。

when instructor student pay for that award test. than instructor student and award_test data will be insert in award_test_payment_notifications. award_test_notifications table have all the field related to student and award tests.

如果指导学生不支付该奖励测试而未付款，则该奖励测试付款

if the instructor student don't pay for that award test than it becomes unpaid, so the award test payment notifications will be nil in that condition only for that award test.

教师学生1注册3个奖项测验，如10、11、12。

教师学生1为奖励测试10支付了现金。

讲师学生1在线支付了奖励测试12。

讲师学生1没有支付奖励测试11。

因此，指导学生有2个奖励测试付款通知
1）奖励测试10 2）奖励测试12

当我搜索2017年1月1日至2017年7月31日之间的奖励测试时，这3个奖项属于此过滤条件
，则讲师学生1将返回3类。 p>

When i search for award tests between 1 jan 2017 to 31 july 2017. and this 3 awards belongs to in this filter than instructor student 1 will be return for 3 categories.

1. 现金付款

1. cash payment

在线付款

未付款

我有2个日期，我需要根据2个日期过滤数据。该日期为考试日期。我必须区分所有现金支付，在线支付和未支付。

i have 2 dates and i need to filter data based on the 2 dates. that date will be test date . i have to differentiate all the cash payments , online payments and unpaid .

这是我为获取现金支付和在线支付所做的查询。

Here is what query i made for getting cash payments and online payments.

current_admin_award_test = AwardTest.joins(instructor_student_awards: {:instructor_student => :award_test_payment_notifications}).where("test_date <= ? AND test_date >= ? AND instructor_student_awards.instructor_student_id  IN (?) ", @endMonth.end_of_month, @startMonth.beginning_of_month, @all_instructor_student_ids)

@award_test_cash = current_admin_award_test.joins("INNER JOIN award_test_payment_notifications atn ON award_test_payment_notifications.award_test_id = award_tests.id").where("award_test_payment_notifications.paid_by = ? ","Cash").select("DISTINCT(award_tests.id), instructor_student_awards.instructor_student_id, award_test_payment_notifications.paid_by, award_tests.test_fee, award_tests.test_date")

@award_test_paid_online = current_admin_award_test.joins("INNER JOIN award_test_payment_notifications atn ON award_test_payment_notifications.award_test_id = award_tests.id").where("award_test_payment_notifications.paid_by <> ? ","Cash").select("DISTINCT(award_tests.id), instructor_student_awards.instructor_student_id, award_test_payment_notifications.paid_by, award_tests.test_fee, award_tests.test_date")

工作正常。但是我被困在如何为一名教师学生获得无偿奖励测试列表的多次奖励测试中。

It is working fine . but i am stuck how to get unpaid award tests list for one instructor students multiple award tests.

更新1

InstructorStudentAward的作用是什么？

what is the role of InstructorStudentAward ?

当讲师学生注册奖励测试时，数据记录在
讲师学生奖励中。

When instructor student register award test. the data logged in instructor student awards.

如果记录了该AwardTest的付款，则该事件

if any payment is logged for that AwardTest, the event is assumed to be paid-in-full. OK?

是的，当指导学生为奖励测试付费时，奖励测试付款
通知将

Yes. when instructor student paid for award test. award test payment notifications will be logged in not matter what is amount.

是否为每一个注册AwardTest的
InstructorStudent创建了一个InstructorStudentAward关联？

Is an InstructorStudentAward association created for every InstructorStudent that registers for an AwardTest?

是的，您是对的。

所以您想要的是任何InstructorStudentAward JOIN AwardTest JOIN
缺少相关的
AwardTestPaymentNotification

So what you want is, any InstructorStudentAward JOIN AwardTest JOIN InstructorStudent pair that is missing an associated AwardTestPaymentNotification

是

我可以假设您是是使用PostgreSQL数据库

Can I assume you're using PostgreSQL database

是的，我正在使用PostgreSQL数据库。

Yes, I am using PostgreSQL database.

推荐答案

我在这个github存储库中添加了我需要（以及您没有提到的一些内容）来复制关联结构的部分问题。

I added this github repo with the parts of your question that I needed (and a few things you didn't mention) to replicate your association structure.

https://github.com/kingdonb/ashvin-stackoverflow-post

example.rb 是一个Rails Runner脚本，我想可以大致复制这些内容是创建并链接在一起的。

example.rb in the project root is a Rails Runner script that I imagine replicates approximately how these things are created and linked together.

您的查询，重新格式化后有点读能够：

Your queries, reformatted to be a bit readable:

current_admin_award_test = AwardTest.joins(
  instructor_student_awards: {
    :instructor_student => :award_test_payment_notifications
  }).where("test_date <= ? AND test_date >= ?
    AND instructor_student_awards.instructor_student_id
    IN (?) ",
  @endMonth.end_of_month,
  @startMonth.beginning_of_month, @all_instructor_student_ids)

@award_test_cash = current_admin_award_test.joins(
  "INNER JOIN award_test_payment_notifications atn ON
  award_test_payment_notifications.award_test_id = 
    award_tests.id").
  where(
    "award_test_payment_notifications.paid_by = ? ", "Cash"
  ).select(
  "DISTINCT(award_tests.id),
   instructor_student_awards.instructor_student_id,
   award_test_payment_notifications.paid_by,
   award_tests.test_fee, award_tests.test_date")

@award_test_paid_online = current_admin_award_test.joins(
  "INNER JOIN award_test_payment_notifications atn ON
  award_test_payment_notifications.award_test_id =
    award_tests.id").
  where(
    "award_test_payment_notifications.paid_by <> ? ","Cash"
  ).select(
  "DISTINCT(award_tests.id),
   instructor_student_awards.instructor_student_id,
   award_test_payment_notifications.paid_by,
   award_tests.test_fee, award_tests.test_date")

我认为丢失的查询不能从 current_admin_award_test 派生，如果您要这样做的话，因为它是在默默地进行内部联接。当您说加入而没有其他上下文时，Rails推断您的意思是内部加入： https ：//apidock.com/rails/ActiveRecord/QueryMethods/joins#docs_body

The missing query I think, CAN'T be derived from current_admin_award_test, if you meant to do that, because it's silently doing an Inner Join. When you say "joins" with no additional context, Rails infers that you mean Inner Join: https://apidock.com/rails/ActiveRecord/QueryMethods/joins#docs_body

查看结果：

current_admin_award_test = AwardTest.joins(
  instructor_student_awards: {
    :instructor_student => :award_test_payment_notifications
  })

您只取回已支付的InstructorStudentAwards！

You get back only paid InstructorStudentAwards!

删除没有匹配的AwardTestPaymentNotifications的记录，因为内部联接需要双方都匹配才能产生行结果。因此，如果您从该关联对象开始查询，它将永远不会返回任何未付款的记录。

So it is dropping records that don't have matching AwardTestPaymentNotifications, because Inner Join requires a match on both sides to produce a row result. So it won't return any records that are unpaid, ever, if you start your query from that association object.

您要：

@award_test_unpaid = AwardTest.left_outer_joins(
  instructor_student_awards: {
    instructor_student: :award_test_payment_notifications
  }).where(award_test_payment_notifications: { id: nil } ).

[ and the rest of your criteria from current_admin_award_test ]

  where("test_date <= ? AND test_date >= ?
    AND instructor_student_awards.instructor_student_id
    IN (?) ",
  @endMonth.end_of_month,
  @startMonth.beginning_of_month, @all_instructor_student_ids)

如果您使用的是旧版的Rails，它将变得更加复杂，因为 #left_outer_joins 仅在Rails 5.0中添加。

If you are using an older version of Rails, it gets more complicated because #left_outer_joins was only added in Rails 5.0.

如果要改善此情况，可以将current_admin_award_test放入不加入 award_test_payment_notifications 的参数化范围内（您已经加入了该表制作每个派生的付款状态列表，我恐怕这会加入两次，这可能就是为什么您不得不诉诸DISTINCT ...？还是还有其他原因吗？）

If you want to improve this, you could make current_admin_award_test into a parameterized scope that does not join award_test_payment_notifications (you are already joining that table to make each derived payment status list, I'm afraid this joins twice and that might be why you had to resort to using DISTINCT...? or was there another reason?)

也许他们为一个AwardTest拥有多个AwardTestPaymentNotifications，而您不想找回重复的AwardT est记录。好。您还可以将此新范围用作 @award_test_unpaid 的基础。

Maybe they had multiple AwardTestPaymentNotifications for one AwardTest and you didn't want to get back duplicate AwardTest records from that. OK. You can also use this new scope as the basis for your @award_test_unpaid.

scope :current_admin_award_test, ->(
  end_date, begin_date, instructor_student_list) {
    joins(instructor_student_awards: :instructor_student).
    where("test_date <= ? AND test_date >= ?",
      end_date.end_of_month, begin_date.beginning_of_month).
    where(instructor_student_awards:
      { instructor_student: instructor_student_list }
    )
  }

（在GitHub项目中 ）我把它放在了AwardTest模型的提交中。

(in context at the GitHub project) I put this in a commit where it goes on the AwardTest model.

您的三个私有变量也可以是具有相同参数的范围（将它们传递到此主范围） ，它处理日期逻辑。）

Your three private variables could also be scopes, with the same parameters (passing them through to this master scope, that handles the date logic.)

（在产生疑问的前提下，您可能已经在模型中包含了这些东西，但是根本没有将它们包括在问题，因为您不想在其中添加过多的代码。）

(Within giving the benefit of the doubt, you might have these things in your model already, but were simply not including them in the question because you didn't want to put too much code in it.)

因此，如果InstructorStudent未支付任何账单，则他的AwardTest将显示在这个清单。并且，如果所有账单都已支付，InstructorStudent将不会显示在此列表中。我想这就是您要实现的目标？

So if an InstructorStudent hasn't paid one of his bills, his AwardTest will show up in this list. And if all of the bills are paid, InstructorStudent will not show up in this list. I think that's what you were trying to achieve?

如果您想进一步导出 InstructorStudent 记录的列表，有未付帐单，因此您可以向他们发送一封措辞强烈的电子邮件，现在看起来像这样：

If you wanted to further derive a list of InstructorStudent records that have unpaid bills, so you can send them each one strongly worded email, now it looks like just like this:

@award_test_unpaid.
  map(&:instructor_student_awards).flatten.
  map(&:instructor_student).
  map(&:id).uniq

为了获得奖金，我花了一些钱在回购上的时间，我站起来，充实Rails的关联和范围，以便有一个current_unpaid范围可以大致满足您的需求。

For a bonus, I just spent some time on the repo I stood up, fleshing out rails associations and scopes so that there is a current_unpaid scope that does roughly what you wanted.

scope :current_unpaid, ->(
  end_date, begin_date, instructor_student_list) {
  current_admin_award_test(end_date, begin_date, instructor_student_list).
    left_outer_joins(:award_test_payment_notifications).
      where(award_test_payment_notifications: { id: nil } )
  }

---

2017-08-04编辑：@ashvin透露他正在使用Rails 4.1，因此 .left_outer_join 将不起作用，因为它没有直到Rails 5才添加。。。尽管如此，原始SQL仍然可以正常工作，

---

2017-08-04 @ashvin revealed he was on Rails 4.1, so .left_outer_join won't work because it wasn't added until Rails 5... this raw SQL will work in its place, though:

 scope :current_unpaid, ->(
   end_date, begin_date, instructor_student_list) {
   current_admin_award_test(end_date, begin_date, instructor_student_list).
     joins("LEFT JOIN award_test_payment_notifications
             ON award_tests.id =
                award_test_payment_notifications.award_test_id
            AND instructor_students.id =
                award_test_payment_notifications.instructor_student_id").
       where(award_test_payment_notifications: { id: nil } )
   }

---

然后您可以

---

Then you can

AwardTest.current_unpaid(Date.today, Date.today, InstructorStudent.all.map(&:id))

并获得当前所有未支付的AwardTest的列表

and get a list of all unpaid AwardTests in the current month!

这取决于在 AwardTest has_many：award_test_payment_notifications $ c>模型。这也取决于我们在上面进一步定义的 current_admin_award_test 范围。使该工作正常进行的所有更改都在此提交中：更多的时间在Rails关联上。希望这会有所帮助！

This depends on setting has_many :award_test_payment_notifications on the AwardTest model. It also depends on the current_admin_award_test scope that we defined further above. All of the changes to make that work are in this commit: more time on rails associations. Hope this helps!

这篇关于查找不存在关联记录的父记录的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！