{"action":"created","comment":{"url":"https://api.github.com/repos/pkp/pkp-lib/pulls/comments/24609201","id":24609201,"diff_hunk":"@@ -452,6 +465,114 @@ function getByStatus($status, $stageUserId = null, $reviewUserId, $contextId = n\n \t}\n \n \t/**\n+\t * Get all submissions that are considered assigned to the passed user.\n+\t * @param $userId int\n+\t * @param $contextId int optional\n+\t * @param $title string|null optional Filter by title.\n+\t * @param $author string|null optional Filter by author.\n+\t * @param $stageId int|null optional Filter by stage id.\n+\t * @param $rangeInfo DBResultRange optional\n+\t * @return DAOResultFactory\n+\t */\n+\tfunction getAssignedToUser($userId, $contextId = null, $title = null, $author = null, $stageId = null, $rangeInfo = null) {\n+\t\t$params = $this->_getFetchParameters();\n+\t\t$userId = (int) $userId;\n+\t\tarray_push($params, $userId, STATUS_DECLINED, $userId, $userId, ROLE_ID_AUTHOR, $userId);\n+\t\tif ($contextId) $params[] = $contextId;\n+\n+\t\tif ($title) {\n+\t\t\t$params[] = AppLocale::getLocale();\n+\t\t\t$params[] = 'title';\n+\t\t\t$params[] = '%' . $title . '%';\n+\t\t}\n+\t\tif ($author) array_push($params, $authorQuery = '%' . $author . '%', $authorQuery, $authorQuery);\n+\t\tif ($stageId) $params[] = $stageId;\n+\n+\t\t$result = $this->retrieveRange($sql =\n+\t\t\t'SELECT s.*, ps.date_published,\n+\t\t\t\t' . $this->_getFetchColumns() . '\n+\t\t\tFROM submissions s\n+\t\t\t\tLEFT JOIN published_submissions ps ON (s.submission_id = ps.submission_id)\n+\t\t\t\tLEFT JOIN stage_assignments sa ON (s.submission_id = sa.submission_id)\n+\t\t\t\tLEFT JOIN submission_files sf ON (s.submission_id = sf.submission_id)\n+\t\t\t\tLEFT JOIN signoffs so ON (sf.file_id = so.assoc_id) \n+\t\t\t\tLEFT JOIN user_groups g ON (g.user_group_id = so.user_group_id)\n+\t\t\t\tLEFT JOIN review_assignments ra ON (s.submission_id = ra.submission_id)\n+\t\t\t\t' . ($title?' LEFT JOIN submission_settings ss ON (s.submission_id = ss.submission_id)':'') . '\n+\t\t\t\t' . ($author?' LEFT JOIN authors au ON (s.submission_id = au.submission_id)':'')\n+\t\t\t\t. $this->_getFetchJoins() .\n+\t\t\t' WHERE s.date_submitted IS NOT NULL AND ps.date_published IS NULL AND s.user_id <> ? AND s.status <> ?\n+\t\t\t\tAND (sa.user_id = ? OR (so.user_id = ? AND g.role_id <> ? ) \n+\t\t\t\tOR ra.reviewer_id = ?)'\n+\t\t\t\t. (($contextId)?' AND s.context_id = ?':'')\n+\t\t\t\t. ($title?' AND (ss.locale = ? AND ss.setting_name = ? AND ss.setting_value LIKE ?)':'') \n+\t\t\t\t. ($author?' AND (au.first_name LIKE ? OR au.middle_name LIKE ? OR au.last_name LIKE ?)':'')\n+\t\t\t\t. ($stageId?' AND s.stage_id = ?':'') .\n+\t\t\t' GROUP BY s.submission_id',\n+\t\t\t$params,\n+\t\t\t$rangeInfo\t\t\t\t\n+\t\t);\n+\n+\t\treturn new DAOResultFactory($result, $this, '_fromRow');\n+\t}\n+\n+\t/**\n+\t * Get all submissions that are assigned to users other than the passed one.\n+\t * @param $userId int\n+\t * @param $contextId int optional\n+\t * @param $title string|null optional Filter by title.\n+\t * @param $author string|null optional Filter by author.\n+\t * @param $editor int|null optional Filter by editor name.\n+\t * @param $stageId int|null optional Filter by stage id.\n+\t * @param $rangeInfo DBResultRange optional\n+\t * @return DAOResultFactory\n+\t */\n+\tfunction getAssignedToOthers($userId, $contextId = null, $title = null, $author = null, $editor = null, $stageId = null, $rangeInfo = null) {\n+\t\t$params = $this->_getFetchParameters();\n+\t\t$userId = (int) $userId;\n+\t\tarray_push($params, $userId, STATUS_DECLINED, $userId, ROLE_ID_MANAGER, ROLE_ID_SUB_EDITOR);\n+\t\tif ($editor) array_push($params, $editorQuery = '%' . $editor . '%', $editorQuery, $editorQuery);\n+\t\tif ($contextId) $params[] = $contextId;\n+\t\t\n+\t\tif ($title) {\n+\t\t\t$params[] = AppLocale::getLocale();\n+\t\t\t$params[] = 'title';\n+\t\t\t$params[] = '%' . $title . '%';\n+\t\t}\n+\t\tif ($author) array_push($params, $authorQuery = '%' . $author . '%', $authorQuery, $authorQuery);\n+\t\tif ($stageId) $params[] = $stageId;\n+\n+\t\t$result = $this->retrieveRange($sql =\n+\t\t\t'SELECT s.*, ps.date_published,\n+\t\t\t\t' . $this->_getFetchColumns() . '\n+\t\t\tFROM submissions s\n+\t\t\t\tLEFT JOIN published_submissions ps ON (s.submission_id = ps.submission_id)\n+\t\t\t\tLEFT JOIN submission_files sf ON (s.submission_id = sf.submission_id)","path":"classes/submission/SubmissionDAO.inc.php","position":130,"original_position":130,"commit_id":"f473e5ba94dfc972518f1430c6bb1651625bdd8f","original_commit_id":"f473e5ba94dfc972518f1430c6bb1651625bdd8f","user":{"login":"asmecher","id":200411,"avatar_url":"https://avatars.githubusercontent.com/u/200411?v=3","gravatar_id":"","url":"https://api.github.com/users/asmecher","html_url":"https://github.com/asmecher","followers_url":"https://api.github.com/users/asmecher/followers","following_url":"https://api.github.com/users/asmecher/following{/other_user}","gists_url":"https://api.github.com/users/asmecher/gists{/gist_id}","starred_url":"https://api.github.com/users/asmecher/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/asmecher/subscriptions","organizations_url":"https://api.github.com/users/asmecher/orgs","repos_url":"https://api.github.com/users/asmecher/repos","events_url":"https://api.github.com/users/asmecher/events{/privacy}","received_events_url":"https://api.github.com/users/asmecher/received_events","type":"User","site_admin":false},"body":"Hmm, that will cause problems with PostgreSQL <9.1.something, as prior to that PostgreSQL required us to actually specify every column. Since then they added smarts to know that grouping by a PRIMARY KEY column was effectively the same thing. I didn't know we were already doing this, but rather than fixing it now, let's keep it consistent.\r\n\r\nJust FYI, an alternate approach that would work without the PostgreSQL requirement is to do two joins:\r\n...LEFT JOIN mytable t ON (...)\r\n...LEFT JOIN mytable t2 ON (... AND t2.my_id > t.my_id)\r\nWHERE\r\nt2.my_id IS NULL\r\n\r\nThe first join brings in the actual content you're looking for, and the second makes sure it's a unique result.","created_at":"2015-02-12T19:03:03Z","updated_at":"2015-02-12T19:03:03Z","html_url":"https://github.com/pkp/pkp-lib/pull/320#discussion_r24609201","pull_request_url":"https://api.github.com/repos/pkp/pkp-lib/pulls/320","_links":{"self":{"href":"https://api.github.com/repos/pkp/pkp-lib/pulls/comments/24609201"},"html":{"href":"https://github.com/pkp/pkp-lib/pull/320#discussion_r24609201"},"pull_request":{"href":"https://api.github.com/repos/pkp/pkp-lib/pulls/320"}}},"pull_request":{"url":"https://api.github.com/repos/pkp/pkp-lib/pulls/320","id":28826972,"html_url":"https://github.com/pkp/pkp-lib/pull/320","diff_url":"https://github.com/pkp/pkp-lib/pull/320.diff","patch_url":"https://github.com/pkp/pkp-lib/pull/320.patch","issue_url":"https://api.github.com/repos/pkp/pkp-lib/issues/320","number":320,"state":"open","locked":false,"title":"*8759* Submissions list improvements","user":{"login":"beghelli","id":593977,"avatar_url":"https://avatars.githubusercontent.com/u/593977?v=3","gravatar_id":"","url":"https://api.github.com/users/beghelli","html_url":"https://github.com/beghelli","followers_url":"https://api.github.com/users/beghelli/followers","following_url":"https://api.github.com/users/beghelli/following{/other_user}","gists_url":"https://api.github.com/users/beghelli/gists{/gist_id}","starred_url":"https://api.github.com/users/beghelli/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/beghelli/subscriptions","organizations_url":"https://api.github.com/users/beghelli/orgs","repos_url":"https://api.github.com/users/beghelli/repos","events_url":"https://api.github.com/users/beghelli/events{/privacy}","received_events_url":"https://api.github.com/users/beghelli/received_events","type":"User","site_admin":false},"body":"","created_at":"2015-02-06T17:39:03Z","updated_at":"2015-02-12T19:03:03Z","closed_at":null,"merged_at":null,"merge_commit_sha":"019d3d03888b5e1b5383dbf9c0ddd1741428c5d5","assignee":null,"milestone":null,"commits_url":"https://api.github.com/repos/pkp/pkp-lib/pulls/320/commits","review_comments_url":"https://api.github.com/repos/pkp/pkp-lib/pulls/320/comments","review_comment_url":"https://api.github.com/repos/pkp/pkp-lib/pulls/comments/{number}","comments_url":"https://api.github.com/repos/pkp/pkp-lib/issues/320/comments","statuses_url":"https://api.github.com/repos/pkp/pkp-lib/statuses/f473e5ba94dfc972518f1430c6bb1651625bdd8f","head":{"label":"beghelli:8759-fix","ref":"8759-fix","sha":"f473e5ba94dfc972518f1430c6bb1651625bdd8f","user":{"login":"beghelli","id":593977,"avatar_url":"https://avatars.githubusercontent.com/u/593977?v=3","gravatar_id":"","url":"https://api.github.com/users/beghelli","html_url":"https://github.com/beghelli","followers_url":"https://api.github.com/users/beghelli/followers","following_url":"https://api.github.com/users/beghelli/following{/other_user}","gists_url":"https://api.github.com/users/beghelli/gists{/gist_id}","starred_url":"https://api.github.com/users/beghelli/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/beghelli/subscriptions","organizations_url":"https://api.github.com/users/beghelli/orgs","repos_url":"https://api.github.com/users/beghelli/repos","events_url":"https://api.github.com/users/beghelli/events{/privacy}","received_events_url":"https://api.github.com/users/beghelli/received_events","type":"User","site_admin":false},"repo":{"id":1314760,"name":"pkp-lib","full_name":"beghelli/pkp-lib","owner":{"login":"beghelli","id":593977,"avatar_url":"https://avatars.githubusercontent.com/u/593977?v=3","gravatar_id":"","url":"https://api.github.com/users/beghelli","html_url":"https://github.com/beghelli","followers_url":"https://api.github.com/users/beghelli/followers","following_url":"https://api.github.com/users/beghelli/following{/other_user}","gists_url":"https://api.github.com/users/beghelli/gists{/gist_id}","starred_url":"https://api.github.com/users/beghelli/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/beghelli/subscriptions","organizations_url":"https://api.github.com/users/beghelli/orgs","repos_url":"https://api.github.com/users/beghelli/repos","events_url":"https://api.github.com/users/beghelli/events{/privacy}","received_events_url":"https://api.github.com/users/beghelli/received_events","type":"User","site_admin":false},"private":false,"html_url":"https://github.com/beghelli/pkp-lib","description":"PKP Web Application Library","fork":true,"url":"https://api.github.com/repos/beghelli/pkp-lib","forks_url":"https://api.github.com/repos/beghelli/pkp-lib/forks","keys_url":"https://api.github.com/repos/beghelli/pkp-lib/keys{/key_id}","collaborators_url":"https://api.github.com/repos/beghelli/pkp-lib/collaborators{/collaborator}","teams_url":"https://api.github.com/repos/beghelli/pkp-lib/teams","hooks_url":"https://api.github.com/repos/beghelli/pkp-lib/hooks","issue_events_url":"https://api.github.com/repos/beghelli/pkp-lib/issues/events{/number}","events_url":"https://api.github.com/repos/beghelli/pkp-lib/events","assignees_url":"https://api.github.com/repos/beghelli/pkp-lib/assignees{/user}","branches_url":"https://api.github.com/repos/beghelli/pkp-lib/branches{/branch}","tags_url":"https://api.github.com/repos/beghelli/pkp-lib/tags","blobs_url":"https://api.github.com/repos/beghelli/pkp-lib/git/blobs{/sha}","git_tags_url":"https://api.github.com/repos/beghelli/pkp-lib/git/tags{/sha}","git_refs_url":"https://api.github.com/repos/beghelli/pkp-lib/git/refs{/sha}","trees_url":"https://api.github.com/repos/beghelli/pkp-lib/git/trees{/sha}","statuses_url":"https://api.github.com/repos/beghelli/pkp-lib/statuses/{sha}","languages_url":"https://api.github.com/repos/beghelli/pkp-lib/languages","stargazers_url":"https://api.github.com/repos/beghelli/pkp-lib/stargazers","contributors_url":"https://api.github.com/repos/beghelli/pkp-lib/contributors","subscribers_url":"https://api.github.com/repos/beghelli/pkp-lib/subscribers","subscription_url":"https://api.github.com/repos/beghelli/pkp-lib/subscription","commits_url":"https://api.github.com/repos/beghelli/pkp-lib/commits{/sha}","git_commits_url":"https://api.github.com/repos/beghelli/pkp-lib/git/commits{/sha}","comments_url":"https://api.github.com/repos/beghelli/pkp-lib/comments{/number}","issue_comment_url":"https://api.github.com/repos/beghelli/pkp-lib/issues/comments/{number}","contents_url":"https://api.github.com/repos/beghelli/pkp-lib/contents/{+path}","compare_url":"https://api.github.com/repos/beghelli/pkp-lib/compare/{base}...{head}","merges_url":"https://api.github.com/repos/beghelli/pkp-lib/merges","archive_url":"https://api.github.com/repos/beghelli/pkp-lib/{archive_format}{/ref}","downloads_url":"https://api.github.com/repos/beghelli/pkp-lib/downloads","issues_url":"https://api.github.com/repos/beghelli/pkp-lib/issues{/number}","pulls_url":"https://api.github.com/repos/beghelli/pkp-lib/pulls{/number}","milestones_url":"https://api.github.com/repos/beghelli/pkp-lib/milestones{/number}","notifications_url":"https://api.github.com/repos/beghelli/pkp-lib/notifications{?since,all,participating}","labels_url":"https://api.github.com/repos/beghelli/pkp-lib/labels{/name}","releases_url":"https://api.github.com/repos/beghelli/pkp-lib/releases{/id}","created_at":"2011-02-01T02:38:54Z","updated_at":"2014-06-24T08:29:49Z","pushed_at":"2015-02-06T17:32:19Z","git_url":"git://github.com/beghelli/pkp-lib.git","ssh_url":"git@github.com:beghelli/pkp-lib.git","clone_url":"https://github.com/beghelli/pkp-lib.git","svn_url":"https://github.com/beghelli/pkp-lib","homepage":"http://pkp.sfu.ca","size":32513,"stargazers_count":1,"watchers_count":1,"language":"PHP","has_issues":false,"has_downloads":true,"has_wiki":true,"has_pages":false,"forks_count":0,"mirror_url":null,"open_issues_count":0,"forks":0,"open_issues":0,"watchers":1,"default_branch":"dev"}},"base":{"label":"pkp:master","ref":"master","sha":"dd5cfb77c97d8ffdffdc880e6254302545569e47","user":{"login":"pkp","id":130896,"avatar_url":"https://avatars.githubusercontent.com/u/130896?v=3","gravatar_id":"","url":"https://api.github.com/users/pkp","html_url":"https://github.com/pkp","followers_url":"https://api.github.com/users/pkp/followers","following_url":"https://api.github.com/users/pkp/following{/other_user}","gists_url":"https://api.github.com/users/pkp/gists{/gist_id}","starred_url":"https://api.github.com/users/pkp/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/pkp/subscriptions","organizations_url":"https://api.github.com/users/pkp/orgs","repos_url":"https://api.github.com/users/pkp/repos","events_url":"https://api.github.com/users/pkp/events{/privacy}","received_events_url":"https://api.github.com/users/pkp/received_events","type":"Organization","site_admin":false},"repo":{"id":316712,"name":"pkp-lib","full_name":"pkp/pkp-lib","owner":{"login":"pkp","id":130896,"avatar_url":"https://avatars.githubusercontent.com/u/130896?v=3","gravatar_id":"","url":"https://api.github.com/users/pkp","html_url":"https://github.com/pkp","followers_url":"https://api.github.com/users/pkp/followers","following_url":"https://api.github.com/users/pkp/following{/other_user}","gists_url":"https://api.github.com/users/pkp/gists{/gist_id}","starred_url":"https://api.github.com/users/pkp/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/pkp/subscriptions","organizations_url":"https://api.github.com/users/pkp/orgs","repos_url":"https://api.github.com/users/pkp/repos","events_url":"https://api.github.com/users/pkp/events{/privacy}","received_events_url":"https://api.github.com/users/pkp/received_events","type":"Organization","site_admin":false},"private":false,"html_url":"https://github.com/pkp/pkp-lib","description":"PKP Web Application Library","fork":false,"url":"https://api.github.com/repos/pkp/pkp-lib","forks_url":"https://api.github.com/repos/pkp/pkp-lib/forks","keys_url":"https://api.github.com/repos/pkp/pkp-lib/keys{/key_id}","collaborators_url":"https://api.github.com/repos/pkp/pkp-lib/collaborators{/collaborator}","teams_url":"https://api.github.com/repos/pkp/pkp-lib/teams","hooks_url":"https://api.github.com/repos/pkp/pkp-lib/hooks","issue_events_url":"https://api.github.com/repos/pkp/pkp-lib/issues/events{/number}","events_url":"https://api.github.com/repos/pkp/pkp-lib/events","assignees_url":"https://api.github.com/repos/pkp/pkp-lib/assignees{/user}","branches_url":"https://api.github.com/repos/pkp/pkp-lib/branches{/branch}","tags_url":"https://api.github.com/repos/pkp/pkp-lib/tags","blobs_url":"https://api.github.com/repos/pkp/pkp-lib/git/blobs{/sha}","git_tags_url":"https://api.github.com/repos/pkp/pkp-lib/git/tags{/sha}","git_refs_url":"https://api.github.com/repos/pkp/pkp-lib/git/refs{/sha}","trees_url":"https://api.github.com/repos/pkp/pkp-lib/git/trees{/sha}","statuses_url":"https://api.github.com/repos/pkp/pkp-lib/statuses/{sha}","languages_url":"https://api.github.com/repos/pkp/pkp-lib/languages","stargazers_url":"https://api.github.com/repos/pkp/pkp-lib/stargazers","contributors_url":"https://api.github.com/repos/pkp/pkp-lib/contributors","subscribers_url":"https://api.github.com/repos/pkp/pkp-lib/subscribers","subscription_url":"https://api.github.com/repos/pkp/pkp-lib/subscription","commits_url":"https://api.github.com/repos/pkp/pkp-lib/commits{/sha}","git_commits_url":"https://api.github.com/repos/pkp/pkp-lib/git/commits{/sha}","comments_url":"https://api.github.com/repos/pkp/pkp-lib/comments{/number}","issue_comment_url":"https://api.github.com/repos/pkp/pkp-lib/issues/comments/{number}","contents_url":"https://api.github.com/repos/pkp/pkp-lib/contents/{+path}","compare_url":"https://api.github.com/repos/pkp/pkp-lib/compare/{base}...{head}","merges_url":"https://api.github.com/repos/pkp/pkp-lib/merges","archive_url":"https://api.github.com/repos/pkp/pkp-lib/{archive_format}{/ref}","downloads_url":"https://api.github.com/repos/pkp/pkp-lib/downloads","issues_url":"https://api.github.com/repos/pkp/pkp-lib/issues{/number}","pulls_url":"https://api.github.com/repos/pkp/pkp-lib/pulls{/number}","milestones_url":"https://api.github.com/repos/pkp/pkp-lib/milestones{/number}","notifications_url":"https://api.github.com/repos/pkp/pkp-lib/notifications{?since,all,participating}","labels_url":"https://api.github.com/repos/pkp/pkp-lib/labels{/name}","releases_url":"https://api.github.com/repos/pkp/pkp-lib/releases{/id}","created_at":"2009-09-24T19:30:31Z","updated_at":"2015-02-11T17:40:10Z","pushed_at":"2015-02-11T23:00:25Z","git_url":"git://github.com/pkp/pkp-lib.git","ssh_url":"git@github.com:pkp/pkp-lib.git","clone_url":"https://github.com/pkp/pkp-lib.git","svn_url":"https://github.com/pkp/pkp-lib","homepage":"http://pkp.sfu.ca","size":47513,"stargazers_count":47,"watchers_count":47,"language":"PHP","has_issues":true,"has_downloads":true,"has_wiki":false,"has_pages":false,"forks_count":114,"mirror_url":null,"open_issues_count":34,"forks":114,"open_issues":34,"watchers":47,"default_branch":"master"}},"_links":{"self":{"href":"https://api.github.com/repos/pkp/pkp-lib/pulls/320"},"html":{"href":"https://github.com/pkp/pkp-lib/pull/320"},"issue":{"href":"https://api.github.com/repos/pkp/pkp-lib/issues/320"},"comments":{"href":"https://api.github.com/repos/pkp/pkp-lib/issues/320/comments"},"review_comments":{"href":"https://api.github.com/repos/pkp/pkp-lib/pulls/320/comments"},"review_comment":{"href":"https://api.github.com/repos/pkp/pkp-lib/pulls/comments/{number}"},"commits":{"href":"https://api.github.com/repos/pkp/pkp-lib/pulls/320/commits"},"statuses":{"href":"https://api.github.com/repos/pkp/pkp-lib/statuses/f473e5ba94dfc972518f1430c6bb1651625bdd8f"}}}}