CODEWITHSUNDEEP
ruby-on-railsactiverecord
jordan
jordan

Reputation: 10812

Activerecord - Sort parent model by child attribute, when child has specific second attribute

I have a table that displays student test scores. I have these classes:

class Student < ApplicationRecord
  has_many :tests
end

class Test < ApplicationRecord
  belongs_to :student
end

The table displays the students' 3 major test scores:

+-------+--------+--------+--------+
| Name  | Test 1 | Test 2 | Test 3 |
+-------+--------+--------+--------+
| Jeff  |   60.5 |   45.9 |  100.0 |
| Cindy |   50.0 |   99.0 |   88.5 |
+-------+--------+--------+--------+

I want to be able to order the students by specific test scores. So I might order desc by the test scores on Test 1 (resulting in [Jeff, Cindy]) or Test 2 (resulting in [Cindy, Jeff]).

I'm not really sure how to incorporate the test name into the query.

Upvotes: 1

Views: 109

Answers (2)

Abhishek
Abhishek

Reputation: 341

joins actually by default is inner join i.e. it'll remove all students who haven't given a test, and all tests without students.

You can try the below code.

# for rails version < 5
Student.joins('LEFT OUTER JOIN "tests" ON "tests"."student_id" = "students"."id"')
       .where(tests: { id: test1_record_id }).order('tests.score desc')

# for rails version >= 5

Student.left_outer_joins(:tests)
       .where(tests: { id: test1_record_id }).order('tests.score desc')

I haven't tested it, but you should get the idea.

EDIT:

You can use the following query:

(SELECT students.*, 
        tests.marks, 
        tests.NAME 
 FROM   students 
        LEFT OUTER JOIN tests 
                     ON tests.student_id = students.id 
 WHERE  tests.NAME = 'test3' 
 ORDER  BY tests.marks DESC) 
UNION ALL 
(SELECT students.*, 
        tests.marks, 
        tests.NAME 
 FROM   students 
        LEFT OUTER JOIN tests 
                     ON tests.student_id = students.id 
 WHERE  tests.NAME != 'test3');

And execute it like ActiveRecord::Base.connection.execute(query).entries

Upvotes: 1

demir
demir

Reputation: 4709

You can sort students like this:

Student.joins(:tests).where(tests: { id: test1_record_id }).order('tests.score desc')

Upvotes: 0

Related Questions