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.

Inextirpable

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
;

Nigelniger

@Erwin Brandstetter Sorry for the late response. still it is not working . i have one record which has monthly detail in only feb and not in jan. but when i search for jan and feb both. it still fetch that record. there is something missing in this query. – Mountebank

@Vishal: I'm pretty confident my query works as advertised. HAVING count(*) = 2 would eliminate students with only 1 matching entry. You would have to post a demo to let us find the misunderstanding. Try dbfidle.uk ... – Nigelniger

@ErwinBrandstetter please check my edited vesrion in question. – Inextirpable

Hi @ErwinBrandstetter , I post another question with another issue. can you please look at it . and solve my problem. it will be very helpful to me. here is question #45251779 – Inextirpable

@ErwinBrandstetter Can you help me out in this question #45706330 ? – Mountebank

Answer 2

You could just convert the months to Ruby's Date class and let ActiveRecord do the work:

due_month= ["2017-01-01","2017-02-01","2017-03-01"]    
fee_paid = InstructorStudent.joins(:fees).where("fees.monthly_detail" => due_month.map{|month| Date.parse month}, "fees.course_type" => "per month")

Hombre