I make 2 queries.
The first:
@queue = Attempt.joins({:problem_info => [:problem, :contest]},
:user).where(‘contests.id’ => 1).limit(100)
The second:
queue = ActiveRecord::Base.connection.query("
SELECT pi.number, p.name “pname”, u.name, a.result, a.exec_time,
a.used_memory, a.time - c.start_time “time”
FROM attempts a
INNER JOIN problem_infos pi ON (pi.id = a.problem_info_id)
INNER JOIN problems p ON (p.id = pi.problem_id)
INNER JOIN users u ON (u.id = a.user_id)
INNER JOIN contests c ON (c.id = pi.contest_id)
WHERE (pi.contest_id = 1)
ORDER BY a.time DESC;")
The first query is better and it is write easy, but it makes several
small requery to database. If i am right, i use resources of Ruby on
Rails.
The seond query make one requery to database.
I want to use the first query, but it works longer than the second. How
to right query, using “join”, but it mustn’t make several requery.