Adding extra SQL queries in a Redmine core view (overriding view in plugin)

Question

I'am overriding a Redmine core view in my plugin (/views/reports/_details.rhtml). Just to print some extra data, like how many open issues are assigned and not assigned.

I tried to override the controller instead and add a method to do this, but I never got it to work, so I added the code below to the view (yes, it's ugly, but the page will rarely by used).

Now to my problem, the view are looping trough all trackers (row.id) and showing information, like how many issues are open and closed. So I have added a extra SQL query, but it only works for the first tracker iteration, for the rest it shows the same data over and over again.

When I look at the development.log there are only one SQL query in it. But when I output row.id (<%= row.id %>) it shows the correct value for each tracker.

How should i solve this?

Code in _details.rhtml

<% @total_assigned_and_open ||=
        ActiveRecord::Base.connection.select_all("select count(i.id) as total
        from
        #{Issue.table_name} i, #{IssueStatus.table_name} s, #{Tracker.table_name} t
        where
        i.status_id=s.id
        and i.tracker_id=#{row.id}
        and i.project_id=#{@project.id}
        and i.assigned_to_id is null
        and s.is_closed = 0
        group by s.id, s.is_closed, t.id limit 1") %>

SQL query in development.log

select count(i.id) as total
from
issues i, issue_statuses s, trackers t
where
i.status_id=s.id
and i.tracker_id=1
and i.project_id=1
and i.assigned_to_id is null
and s.is_closed = 0
group by s.id, s.is_closed, t.id limit 1

(Never used Ruby on Rails or Redmine before...)

Answer 1

I solved it by adding two SQL queries (in the view) that selects all issues to the selected project.

<% @all_unsigned_issues ||=
ActiveRecord::Base.connection.select_all("select i.status_id as status, s.is_closed as 
closed, t.id as tracker_id, count(i.id) as total
from
#{Issue.table_name} i, #{IssueStatus.table_name} s, #{Tracker.table_name} t 
where
i.assigned_to_id is null
and i.project_id=#{@project.id}
and i.status_id=s.id
and i.tracker_id=t.id
group by i.id") %>

Then I use.

<%= aggregate_link @all_unsigned_issues, { "tracker_id" => row.id, "status" => 5 },
            :controller => 'issues', :action => 'index', :project_id => ((row.is_a?(Project) ? row : @project)) %>