500 Internal Server Error / Saved queries with grouping parameter

[traedamatic]

Hello,

first of all thank you for this very nice plugin. It really solves a issue for us. But unfortunately we get an error when we navigate to a saved issue query that has a grouping parameter selected.

Started GET "/projects/projectone/issues_trees/tree_index?c%5B%5D=tracker&c%5B%5D=status&c%5B%5D=priority&c%5B%5D=subject&c%5B%5D=category&c%5B%5D=assigned_to&c%5B%5D=due_date&c%5B%5D=updated_on&f%5B%5D=status_id&f%5B%5D=category_id&f%5B%5D=&op%5Bcategory_id%5D=%3D&op%5Bstatus_id%5D=o&query_id=28&set_filter=1&v%5Bcategory_id%5D%5B%5D=34" for 80.153.23.76 at 2015-11-09 09:02:26 +0100
Processing by IssuesTreesController#tree_index as HTML
  Parameters: {"c"=>["tracker", "status", "priority", "subject", "category", "assigned_to", "due_date", "updated_on"], "f"=>["status_id", "category_id", ""], "op"=>{"category_id"=>"=", "status_id"=>"o"}, "query_id"=>"28", "set_filter"=>"1", "v"=>{"category_id"=>["34"]}, "project_id"=>"admiles-app"}
  Current user: user (id=12)
Mysql2::Error: Unknown column 'versions.effective_date' in 'order clause': SELECT `issues`.`id` AS t0_r0, `issues`.`tracker_id` AS t0_r1, `issues`.`project_id` AS t0_r2, `issues`.`subject` AS t0_r3, `issues`.`description` AS t0_r4, `issues`.`due_date` AS t0_r5, `issues`.`category_id` AS t0_r6, `issues`.`status_id` AS t0_r7, `issues`.`assigned_to_id` AS t0_r8, `issues`.`priority_id` AS t0_r9, `issues`.`fixed_version_id` AS t0_r10, `issues`.`author_id` AS t0_r11, `issues`.`lock_version` AS t0_r12, `issues`.`created_on` AS t0_r13, `issues`.`updated_on` AS t0_r14, `issues`.`start_date` AS t0_r15, `issues`.`done_ratio` AS t0_r16, `issues`.`estimated_hours` AS t0_r17, `issues`.`parent_id` AS t0_r18, `issues`.`root_id` AS t0_r19, `issues`.`lft` AS t0_r20, `issues`.`rgt` AS t0_r21, `issues`.`is_private` AS t0_r22, `issues`.`closed_on` AS t0_r23, `issues`.`project_key` AS t0_r24, `issues`.`issue_number` AS t0_r25, `issue_statuses`.`id` AS t1_r0, `issue_statuses`.`name` AS t1_r1, `issue_statuses`.`is_closed` AS t1_r2, `issue_statuses`.`position` AS t1_r3, `issue_statuses`.`default_done_ratio` AS t1_r4, `projects`.`id` AS t2_r0, `projects`.`name` AS t2_r1, `projects`.`description` AS t2_r2, `projects`.`homepage` AS t2_r3, `projects`.`is_public` AS t2_r4, `projects`.`parent_id` AS t2_r5, `projects`.`created_on` AS t2_r6, `projects`.`updated_on` AS t2_r7, `projects`.`identifier` AS t2_r8, `projects`.`status` AS t2_r9, `projects`.`lft` AS t2_r10, `projects`.`rgt` AS t2_r11, `projects`.`inherit_members` AS t2_r12, `projects`.`issue_key` AS t2_r13 FROM `issues` INNER JOIN `projects` ON `projects`.`id` = `issues`.`project_id` INNER JOIN `issue_statuses` ON `issue_statuses`.`id` = `issues`.`status_id` WHERE (projects.status <> 9 AND projects.id IN (SELECT em.project_id FROM enabled_modules em WHERE em.name='issue_tracking')) AND ((issues.status_id IN (SELECT id FROM issue_statuses WHERE is_closed=0)) AND projects.id = 18)  ORDER BY (CASE WHEN versions.effective_date IS NULL THEN 1 ELSE 0 END) ,versions.effective_date ,versions.name ,versions.id 
Completed 500 Internal Server Error in 44ms (ActiveRecord: 5.6ms)

Query::StatementInvalid (Mysql2::Error: Unknown column 'versions.effective_date' in 'order clause': SELECT `issues`.`id` AS t0_r0, `issues`.`tracker_id` AS t0_r1, `issues`.`project_id` AS t0_r2, `issues`.`subject` AS t0_r3, `issues`.`description` AS t0_r4, `issues`.`due_date` AS t0_r5, `issues`.`category_id` AS t0_r6, `issues`.`status_id` AS t0_r7, `issues`.`assigned_to_id` AS t0_r8, `issues`.`priority_id` AS t0_r9, `issues`.`fixed_version_id` AS t0_r10, `issues`.`author_id` AS t0_r11, `issues`.`lock_version` AS t0_r12, `issues`.`created_on` AS t0_r13, `issues`.`updated_on` AS t0_r14, `issues`.`start_date` AS t0_r15, `issues`.`done_ratio` AS t0_r16, `issues`.`estimated_hours` AS t0_r17, `issues`.`parent_id` AS t0_r18, `issues`.`root_id` AS t0_r19, `issues`.`lft` AS t0_r20, `issues`.`rgt` AS t0_r21, `issues`.`is_private` AS t0_r22, `issues`.`closed_on` AS t0_r23, `issues`.`project_key` AS t0_r24, `issues`.`issue_number` AS t0_r25, `issue_statuses`.`id` AS t1_r0, `issue_statuses`.`name` AS t1_r1, `issue_statuses`.`is_closed` AS t1_r2, `issue_statuses`.`position` AS t1_r3, `issue_statuses`.`default_done_ratio` AS t1_r4, `projects`.`id` AS t2_r0, `projects`.`name` AS t2_r1, `projects`.`description` AS t2_r2, `projects`.`homepage` AS t2_r3, `projects`.`is_public` AS t2_r4, `projects`.`parent_id` AS t2_r5, `projects`.`created_on` AS t2_r6, `projects`.`updated_on` AS t2_r7, `projects`.`identifier` AS t2_r8, `projects`.`status` AS t2_r9, `projects`.`lft` AS t2_r10, `projects`.`rgt` AS t2_r11, `projects`.`inherit_members` AS t2_r12, `projects`.`issue_key` AS t2_r13 FROM `issues` INNER JOIN `projects` ON `projects`.`id` = `issues`.`project_id` INNER JOIN `issue_statuses` ON `issue_statuses`.`id` = `issues`.`status_id` WHERE (projects.status <> 9 AND projects.id IN (SELECT em.project_id FROM enabled_modules em WHERE em.name='issue_tracking')) AND ((issues.status_id IN (SELECT id FROM issue_statuses WHERE is_closed=0)) AND projects.id = 18)  ORDER BY (CASE WHEN versions.effective_date IS NULL THEN 1 ELSE 0 END) ,versions.effective_date ,versions.name ,versions.id ):
  app/models/issue_query.rb:355:in `rescue in issues'
  app/models/issue_query.rb:328:in `issues'
  lib/redmine/sudo_mode.rb:63:in `sudo_mode'

I already looked in the code but my knowledge of the redmine code is very limited. So maybe you save a easy solution for this bug?

Nicolas

[Loriowar]

Sorry, but for now plugin incompatible with grouping. I can't imagine how tree must look in this case. Initially I planned to remove all grouping conditions from query in case of tree view. Maybe something goes wrong now. I try to find a problem of 500 error in closest future.

[Loriowar]

Hope I fix your problem. Please try repeat this problem using master branch of plugin. If it is ok, I'll add changes in all supported versions.

[traedamatic]

Hello, the master branch version shows the red error message and disables the grouping parameter. This is a first solution. But you a right, how can you build a tree if a parent ticket and a child ticket are in different grouping sections. :(

[Loriowar]

Yes, this is a problem. If you can invent any suitable solution, I'll be glad to implement it into plugin.