Rails的WHERE子句中的ALL运算符

问题描述

关联如下所示。

InstructorStudent has_many :fees

Fee belongs_to :instructor_student

我想让在每个给定数组中都有每月详细信息的讲师学生。如果其中任何一个都不包含每月详细信息，则它不应返回任何记录。

I want to get the instructor student who has monthly detail in all given array. If monthly details is not present in any one of them then it should not return any record.

due_month = ["2017-01-01","2017-02-01",,"2017-03-01"]

以下是我尝试过的查询，我想获取 InstructorStudent ，它属于给定的所有三个Due_month，如果任何月份没有数据，则应返回零：

Following is the query which I tried, I want to get InstructorStudent which belongs to all given three due_month, if any month have no data then it should return nil:

@fee_paid = 
InstructorStudent.first.joins(:fees).where("fees.monthly_detail = 
ALL(ARRAY[?]::date[]) AND fees.course_type = ?", due_month.map{|i| i 
},"per month");

编辑1：

@ erwin-brandstetter这是我的最后一个查询

@erwin-brandstetter here is my final query

InstructorStudent.where("
  instructor_students.Id IN (?)",Instructor.find(17).per_month_active_student
).joins(
  "INNER JOIN fees ON fees.instructor_student_id = instructor_students.id LEFT OUTER JOIN fee_payment_notifications ON fee_payment_notifications.fee_id = fees.id"
).where(
  "fee_payment_notifications.status <> ? AND
  fees.monthly_detail = ANY(ARRAY[?]::date[]) AND
  fees.course_type = ? AND
  fees.payment_status <> ?"
  , 'Paid',dueMonth,"per month", "Due"
).group(
  'fees.instructor_student_id'
).
having(
  'count(*) = ?', dueMonth.length
)

协会：

InstructorStudent has_many Fees
Fee belongs_to instructor_student

Fee has_many fee_payment_notifications
FeePaymentNotifications belongs to fee

在这里，我为获取指导学生做些什么。其中的fee.monthly_detail存在于dueMonth数组中，并且fee.payment_status为到期，而Fees.course_type为每月
，并且fee_payment_notifications不应为已付费。

Here What I do for fetching instructor students. which has fees.monthly_detail present in array of dueMonth and fees.payment_status is "Due" and Fees.course_type is "per month" and fee_payment_notifications should not be "Paid".

不一定要始终存在fee_payment_notifications。
因此，如果fee具有fee_payment_notifications，则仅应检查其状态。
如果没有任何fee_payment_notifications，则应获取记录以外的记录。
如果有任何fee_payment_notifications并且状态为已付费，则不应获取记录。

推荐答案

这是

• How to filter SQL results in a has-many-through relation

如您所见，我根本没有涉及 instructor_student 表。尽管参照完整性是通过FK约束来强制执行的（通常如此），但我们可以单独使用 fees 来确定合格的 instructor_student_id 。如果您需要从主表中获取更多属性，请执行第二步，例如：

As you can see, I did not involve the table instructor_student at all. While referential integrity is enforced with a FK constraint (like it typically is), we can work with fees alone to determine qualifying instructor_student_id. If you need to fetch more attributes from the main table, do that in a 2nd step, like:

SELECT i.*  -- or whatever you need
FROM   instructor_student i
JOIN  (
   SELECT ...  -- query from above
   ) f ON f.instructor_student_id = i.id
;

这篇关于Rails的WHERE子句中的ALL运算符的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！