[SOLVED] Fetch records from multiple tables by JOIN in rails 2

Fetch records from multiple tables by JOIN in rails 2

I have my records set up as follows

UserKycScanResults: id, user_kyc_scan_id, match_name  <- belongs to UserKycScan
UserKycScan: id, user_id, created_at                  <- belongs to User
User: id, firstname, lastname

I'm trying to write a join so that I can find users' firstname who have a entry in the user_kyc_scans table and user_kyc_scan table entry date is yesterday along with their match_name from user_kyc_scan_results table if present.

I have tried the following

UserKycScanResult.joins("INNER JOIN user_kyc_scans ON user_kyc_scans.id = user_kyc_scan_results.user_kyc_scan_id
                INNER JOIN users ON users.id = user_kyc_scans.user_id").where("users.name = '#{submitted_first_name}', user_kyc_scans.created_at = #{Date.yesterday} ")

But this approach doesn't work. Any help would be appreciated. And joins and where clauses are not working on rails 2

Please give me any solution to sort this out

Thanks.

Solution

Your inner joins query are right but in rails 2 we have to pass joins and conditions these way:

UserKycScanResult.all(:joins => "INNER JOIN user_kyc_scans ON user_kyc_scans.id = user_kyc_scan_results.user_kyc_scan_id 
                                 INNER JOIN users ON users.id = user_kyc_scans.user_id", 
                      :conditions => ["users.name = ? AND user_kyc_scans.created_at = ?", submitted_first_name, Date.yesterday])

Document for reference: ruby on rails guides

Happy Coding