Here is the association what i have .
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.
online
cash
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.
instructor student 1 register for 3 award tests like 10,11,12.
instructor student 1 paid cash for award test 10.
instructor student 1 paid online for award test 12.
instructor student 1 didn't paid for award test 11.
so instructor student has 2 award test payment notifications 1) for award test 10 2) award test 12
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.
cash payment
online payment
unpaid
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.
Update 1
what is the role of InstructorStudentAward ?
When instructor student register award test. the data logged in instructor student awards.
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.
Is an InstructorStudentAward association created for every InstructorStudent that registers for an AwardTest?
Yes , you are right .
So what you want is, any InstructorStudentAward JOIN AwardTest JOIN InstructorStudent pair that is missing an associated AwardTestPaymentNotification
Yes
Can I assume you're using PostgreSQL database
Yes, I am using PostgreSQL database.