Postgresql query error

[burci]

ActiveRecord::StatementInvalid (PG::Error: ERROR: column "issues.tracker_id" must appear in the GROUP BY clause or be used in an aggregate function LINE 1: SELECT "issues"."id" AS t0_r0, "issues"."tracker_id" AS t0_r... ^ : 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, "issue_statuses"."id" AS t1_r0, "issue_statuses"."name" AS t1_r1, "issue_statuses"."is_closed" AS t1_r2, "issue_statuses"."is_default" AS t1_r3, "issue_statuses"."position" AS t1_r4, "issue_statuses"."default_done_ratio" AS t1_r5, "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, "trackers"."id" AS t3_r0, "trackers"."name" AS t3_r1, "trackers"."is_in_chlog" AS t3_r2, "trackers"."position" AS t3_r3, "trackers"."is_in_roadmap" AS t3_r4, "enumerations"."id" AS t4_r0, "enumerations"."name" AS t4_r1, "enumerations"."position" AS t4_r2, "enumerations"."is_default" AS t4_r3, "enumerations"."type" AS t4_r4, "enumerations"."active" AS t4_r5, "enumerations"."project_id" AS t4_r6, "enumerations"."parent_id" AS t4_r7, "time_entries"."id" AS t5_r0, "time_entries"."project_id" AS t5_r1, "time_entries"."user_id" AS t5_r2, "time_entries"."issue_id" AS t5_r3, "time_entries"."hours" AS t5_r4, "time_entries"."comments" AS t5_r5, "time_entries"."activity_id" AS t5_r6, "time_entries"."spent_on" AS t5_r7, "time_entries"."tyear" AS t5_r8, "time_entries"."tmonth" AS t5_r9, "time_entries"."tweek" AS t5_r10, "time_entries"."created_on" AS t5_r11, "time_entries"."updated_on" AS t5_r12 FROM "issues" LEFT OUTER JOIN "issue_statuses" ON "issue_statuses".id = "issues".status_id LEFT OUTER JOIN "projects" ON "projects".id = "issues".project_id LEFT OUTER JOIN "trackers" ON "trackers".id = "issues".tracker_id LEFT OUTER JOIN "enumerations" ON "enumerations".id = "issues".priority_id AND ("enumerations"."type" = 'IssuePriority' ) LEFT OUTER JOIN "time_entries" ON time_entries.issue_id = issues.id WHERE ((assigned_to_id=1 or time_entries.user_id=1) AND issue_statuses.is_closed='f' AND projects.status=1 AND projects.id=1) GROUP BY issues.id ORDER BY issues.id DESC, issues.updated_on DESC):

vendor/plugins/redmine_spent_time/app/helpers/spent_time_helper.rb:14:in `find_assigned_issues_by_project'

vendor/plugins/redmine_spent_time/app/controllers/spent_time_controller.rb:91:in `update_project_issues'

Rendering .../redmine/public/500.html (500 Internal Server Error)

[eyp]

Hi, why must I group by 'tracker_id'?

[burci]

Hi!

I think it works on mysql because of "MySQL's totally non standards compliant GROUP BY". :)

http://stackoverflow.com/questions/1769361/postgresql-group-by-different-from-mysql

I think only the issues should be loaded by this query and after then get all the other data or use DISTINCT.

[eyp]

Ok, I'll revise it, but noway I'll get issues then get the rest of data.... if I can do it in a query :)

[burci]

Well if you really need all of this data and don't want to use object hierarchy to get it, then maybe DISTINCT in SQL could work:

SELECT

 DISTINCT

 "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,
 "issue_statuses"."id" AS t1_r0,
 "issue_statuses"."name" AS t1_r1,
 "issue_statuses"."is_closed" AS t1_r2,
 "issue_statuses"."is_default" AS t1_r3,
 "issue_statuses"."position" AS t1_r4,
 "issue_statuses"."default_done_ratio" AS t1_r5,
 "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,
 "trackers"."id" AS t3_r0,
 "trackers"."name" AS t3_r1,
 "trackers"."is_in_chlog" AS t3_r2,
 "trackers"."position" AS t3_r3,
 "trackers"."is_in_roadmap" AS t3_r4,
 "enumerations"."id" AS t4_r0,
 "enumerations"."name" AS t4_r1,
 "enumerations"."position" AS t4_r2,
 "enumerations"."is_default" AS t4_r3,
 "enumerations"."type" AS t4_r4,
 "enumerations"."active" AS t4_r5,
 "enumerations"."project_id" AS t4_r6,
 "enumerations"."parent_id" AS t4_r7,
 "time_entries"."id" AS t5_r0,
 "time_entries"."project_id" AS t5_r1,
 "time_entries"."user_id" AS t5_r2,
 "time_entries"."issue_id" AS t5_r3,
 "time_entries"."hours" AS t5_r4,
 "time_entries"."comments" AS t5_r5,
 "time_entries"."activity_id" AS t5_r6,
 "time_entries"."spent_on" AS t5_r7,
 "time_entries"."tyear" AS t5_r8,
 "time_entries"."tmonth" AS t5_r9,
 "time_entries"."tweek" AS t5_r10,
 "time_entries"."created_on" AS t5_r11,
 "time_entries"."updated_on" AS t5_r12 
FROM "issues" 
    LEFT OUTER JOIN "issue_statuses" ON "issue_statuses".id = "issues".status_id
    LEFT OUTER JOIN "projects" ON "projects".id = "issues".project_id 
    LEFT OUTER JOIN "trackers" ON "trackers".id = "issues".tracker_id 
    LEFT OUTER JOIN "enumerations" ON "enumerations".id = "issues".priority_id AND ("enumerations"."type" = 'IssuePriority' ) 
    LEFT OUTER JOIN "time_entries" ON time_entries.issue_id = issues.id
WHERE ((assigned_to_id=1 or time_entries.user_id=1) AND issue_statuses.is_closed='f' AND projects.status=1 AND projects.id=1)
ORDER BY issues.id DESC, issues.updated_on DESC;

(If the aim is that one issue appear only once in this list, then this is not the good solution in case if there is more then one time_entries where time_entries.user_id=1. The original query works only in non standard GROUP BY implementation like MYSQL, which gives the first row of non aggregated or grouped data rows. )

Anyway the function name is "find_assigned_issues_by_project", this is a bit confusing if you don`t want to get only issues.

If you got any solution i can test it on postgresql.

[eyp]

Thank you @burci for your help.

On the other hand, I think the name is correct. I want to find Issues.... and their related information (status, type & entry times). An issue without the rest of the data is useless for this functionality ;)

[burci]

Ok i fixed it and made a pull request. You dont need the group by, the rails active record make everything for you.

[eyp]

HI @burci, how does AR solve it?

[burci]

Hi @eyp!

Because you make an Issue.find() call, AR hydrating will take care of the list and deduplicate it if it needs. I added a pull request.

[eyp]

Yes, I've seen your PR, thank you. On the other hand, I didn't know that Rails feature.... then, how might I do a query which returns repeated records?

[burci]

Well this is the first time i met ruby on rails.. :) But usually AR and other ORM will hydrating SQL rows into entites. So if you make a call like Issue.find() then you will get a list of Issues. At this find_assigned_issues_by_project function i think you should not give back the same issue more then once (i think that can happen only if an Issue have more then one TimeEntry by an user)...

In my test cases i cannot see any duplication in the list.

(Or maybe options_for_select(@assigned_issues.collect { |i| ["##{i.id} - #{i.subject}", i.id] }) deduplicates the list.)