ALL operator in WHERE clause in Rails

Question

The association is as shown below.

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"]

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");

Edit 1:

@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
)

Associations:

InstructorStudent has_many Fees
Fee belongs_to instructor_student

Fee has_many fee_payment_notifications
FeePaymentNotifications belongs to fee

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".

It is not compulsory that there is always fee_payment_notifications is present. So, If fee has fee_payment_notifications than only it should check for its status. If there is no any fee_payment_notifications than record should be fetched. If there is any fee_payment_notifications and status is "Paid" than record should not be fetched.

Answer 1

That's a case of relational-division.

Actual table definitions (standard 1:n relationship, hidden by the Ruby ORM) will be something like this:

CREATE TABLE instructor_student (
   id serial PRIMARY KEY
   name ...
);

CREATE TABLE fees (
   id serial PRIMARY KEY
 , instructor_student_id integer NOT NULL REFERENCES instructor_student
 , course_type ...
 , monthly_detail date
 , UNIQUE (instructor_student_id, course_type, monthly_detail)
);

Your attempt at a query effectively tries to test each single row in fees against multiple values in the given array, which always fails while elements of the array are not identical. One value cannot be the same as multiple other values. You need a different approach:

SELECT instructor_student_id
FROM   fees
WHERE  course_type = ?
AND    monthly_detail = ANY(ARRAY[?]::date[])  -- ANY, not ALL!
GROUP  BY instructor_student_id
HAVING count(*) = cardinality(ARRAY[?]::date[]);

This is assuming distinct values in your array and unique entries in your table fees like enforced by the UNIQUE constraint I added above. Else, counts are not reliable and you have to use a more sophisticated query. Here is an arsenal of options:

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

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
;