{"action":"created","comment":{"url":"https://api.github.com/repos/joomla/joomla-cms/pulls/comments/88555693","pull_request_review_id":9127228,"id":88555693,"diff_hunk":"@@ -1354,43 +1354,37 @@ public function reorder($where = '')\n \t\t\tthrow new UnexpectedValueException(sprintf('%s does not support ordering.', get_class($this)));\n \t\t}\n \n-\t\t$k = $this->_tbl_key;\n+\t\t$subquery = $this->_db->getQuery(true)\n+\t\t\t->from($this->_tbl)\n+\t\t\t->windowRowNumber('ordering', 'new_ordering');","path":"libraries/joomla/table/table.php","position":7,"original_position":7,"commit_id":"edb8c3b1e8082eb31e5cee9438e0d3dbc7b967ff","original_commit_id":"edb8c3b1e8082eb31e5cee9438e0d3dbc7b967ff","user":{"login":"csthomas","id":9054379,"avatar_url":"https://avatars.githubusercontent.com/u/9054379?v=3","gravatar_id":"","url":"https://api.github.com/users/csthomas","html_url":"https://github.com/csthomas","followers_url":"https://api.github.com/users/csthomas/followers","following_url":"https://api.github.com/users/csthomas/following{/other_user}","gists_url":"https://api.github.com/users/csthomas/gists{/gist_id}","starred_url":"https://api.github.com/users/csthomas/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/csthomas/subscriptions","organizations_url":"https://api.github.com/users/csthomas/orgs","repos_url":"https://api.github.com/users/csthomas/repos","events_url":"https://api.github.com/users/csthomas/events{/privacy}","received_events_url":"https://api.github.com/users/csthomas/received_events","type":"User","site_admin":false},"body":"I take a look.","created_at":"2016-11-17T21:55:39Z","updated_at":"2016-11-17T21:55:39Z","html_url":"https://github.com/joomla/joomla-cms/pull/12839#discussion_r88555693","pull_request_url":"https://api.github.com/repos/joomla/joomla-cms/pulls/12839","_links":{"self":{"href":"https://api.github.com/repos/joomla/joomla-cms/pulls/comments/88555693"},"html":{"href":"https://github.com/joomla/joomla-cms/pull/12839#discussion_r88555693"},"pull_request":{"href":"https://api.github.com/repos/joomla/joomla-cms/pulls/12839"}}},"pull_request":{"url":"https://api.github.com/repos/joomla/joomla-cms/pulls/12839","id":92814873,"html_url":"https://github.com/joomla/joomla-cms/pull/12839","diff_url":"https://github.com/joomla/joomla-cms/pull/12839.diff","patch_url":"https://github.com/joomla/joomla-cms/pull/12839.patch","issue_url":"https://api.github.com/repos/joomla/joomla-cms/issues/12839","number":12839,"state":"open","locked":false,"title":"[RFC] Another way to fast reorder table with single SQL query","user":{"login":"csthomas","id":9054379,"avatar_url":"https://avatars.githubusercontent.com/u/9054379?v=3","gravatar_id":"","url":"https://api.github.com/users/csthomas","html_url":"https://github.com/csthomas","followers_url":"https://api.github.com/users/csthomas/followers","following_url":"https://api.github.com/users/csthomas/following{/other_user}","gists_url":"https://api.github.com/users/csthomas/gists{/gist_id}","starred_url":"https://api.github.com/users/csthomas/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/csthomas/subscriptions","organizations_url":"https://api.github.com/users/csthomas/orgs","repos_url":"https://api.github.com/users/csthomas/repos","events_url":"https://api.github.com/users/csthomas/events{/privacy}","received_events_url":"https://api.github.com/users/csthomas/received_events","type":"User","site_admin":false},"body":"Pull Request for Issue https://github.com/joomla/joomla-cms/issues/10567.\r\n\r\nRelated PRs #11184 and #8563\r\n\r\n### Summary of Changes\r\n\r\n* Add a new method to `JDatabaseQuery` `windowRowNumber()` with support for mysql, postgresql>=8.4, mssql and probably oracle but not by SQLite.\r\n* Do not load table rows to php memory. Use multi-table `UPDATE` syntax to reorder table in one sql query.\r\n* ~~Multi-table `UPDATE` syntax is supported by mssql but joomla does not support it for now.~~\r\n* Performance improvement should be very good but I have not tested it yet. You are welcome.\r\n* Fixes for postgreSQL. Each call for `(string) $query;` returned different result.\r\n* Fixes for SQLSRV. Multi table update generated invalid query.\r\n* Turn off unittest for reorder method because it uses SQLite database. \r\n\r\n### TO DO\r\n* Unit test for windowRowNumber still missing for query test\r\n\r\n### Questions\r\n* Is complicated solution is acceptable? I mean using window function which is not supported by SQLite.\r\n* Should I separate that PR into a few PRs? \r\n* I'm not sure whether implementation SQLite equivalent for multi table update or `windowRowNumber` is reasonable.\r\n\r\n### Testing Instructions\r\nJust as in a competitive PR #11184.\r\n\r\nIn short: \r\n* Create a new article in a category with a lots of articles. Patched version should be faster but you may not see it if you have less than 1000 articles in the category.\r\n* Check that your new article is on the top.\r\n* Do the same for featured article. You should have a lots of featured articles to see time difference.\r\n\r\nI have tested this patch only on Content component, means `#__content` and `#__content_frontpage` tables for mysql and postgresql 9.5 installation.\r\n\r\nNote about joomla:\r\n* Content items start ordering from 0.\r\n* Featured items start ordering from 1.\r\n\r\nThe test to be more enjoyable, you can add some code after `$query->execute()`\r\nhttps://github.com/joomla/joomla-cms/pull/12839/files#diff-8254e2c441a41d3fa26f0f83fbbb3043R1390\r\n\r\n```php\r\nJFactory::getApplication()->enqueueMessage(\r\n'<b>reorder()</b>:<br/>'.$this->_db->replacePrefix($query).'<br/>Affected:'.$this->_db->getAffectedRows(), 'message');\r\n```\r\n\r\n### Documentation Changes Required\r\nProbably.\r\nIntruduce a new method `JDatabaseQuery::windowRowNumber()`\r\n","created_at":"2016-11-08T16:44:32Z","updated_at":"2016-11-17T21:55:39Z","closed_at":null,"merged_at":null,"merge_commit_sha":"7d614536f186595fcecd7d7dd502e695988eb5b6","assignee":null,"assignees":[],"milestone":null,"commits_url":"https://api.github.com/repos/joomla/joomla-cms/pulls/12839/commits","review_comments_url":"https://api.github.com/repos/joomla/joomla-cms/pulls/12839/comments","review_comment_url":"https://api.github.com/repos/joomla/joomla-cms/pulls/comments{/number}","comments_url":"https://api.github.com/repos/joomla/joomla-cms/issues/12839/comments","statuses_url":"https://api.github.com/repos/joomla/joomla-cms/statuses/edb8c3b1e8082eb31e5cee9438e0d3dbc7b967ff","head":{"label":"csthomas:row_number","ref":"row_number","sha":"edb8c3b1e8082eb31e5cee9438e0d3dbc7b967ff","user":{"login":"csthomas","id":9054379,"avatar_url":"https://avatars.githubusercontent.com/u/9054379?v=3","gravatar_id":"","url":"https://api.github.com/users/csthomas","html_url":"https://github.com/csthomas","followers_url":"https://api.github.com/users/csthomas/followers","following_url":"https://api.github.com/users/csthomas/following{/other_user}","gists_url":"https://api.github.com/users/csthomas/gists{/gist_id}","starred_url":"https://api.github.com/users/csthomas/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/csthomas/subscriptions","organizations_url":"https://api.github.com/users/csthomas/orgs","repos_url":"https://api.github.com/users/csthomas/repos","events_url":"https://api.github.com/users/csthomas/events{/privacy}","received_events_url":"https://api.github.com/users/csthomas/received_events","type":"User","site_admin":false},"repo":{"id":45381530,"name":"joomla-cms","full_name":"csthomas/joomla-cms","owner":{"login":"csthomas","id":9054379,"avatar_url":"https://avatars.githubusercontent.com/u/9054379?v=3","gravatar_id":"","url":"https://api.github.com/users/csthomas","html_url":"https://github.com/csthomas","followers_url":"https://api.github.com/users/csthomas/followers","following_url":"https://api.github.com/users/csthomas/following{/other_user}","gists_url":"https://api.github.com/users/csthomas/gists{/gist_id}","starred_url":"https://api.github.com/users/csthomas/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/csthomas/subscriptions","organizations_url":"https://api.github.com/users/csthomas/orgs","repos_url":"https://api.github.com/users/csthomas/repos","events_url":"https://api.github.com/users/csthomas/events{/privacy}","received_events_url":"https://api.github.com/users/csthomas/received_events","type":"User","site_admin":false},"private":false,"html_url":"https://github.com/csthomas/joomla-cms","description":"Home of the Joomla! Content Management System","fork":true,"url":"https://api.github.com/repos/csthomas/joomla-cms","forks_url":"https://api.github.com/repos/csthomas/joomla-cms/forks","keys_url":"https://api.github.com/repos/csthomas/joomla-cms/keys{/key_id}","collaborators_url":"https://api.github.com/repos/csthomas/joomla-cms/collaborators{/collaborator}","teams_url":"https://api.github.com/repos/csthomas/joomla-cms/teams","hooks_url":"https://api.github.com/repos/csthomas/joomla-cms/hooks","issue_events_url":"https://api.github.com/repos/csthomas/joomla-cms/issues/events{/number}","events_url":"https://api.github.com/repos/csthomas/joomla-cms/events","assignees_url":"https://api.github.com/repos/csthomas/joomla-cms/assignees{/user}","branches_url":"https://api.github.com/repos/csthomas/joomla-cms/branches{/branch}","tags_url":"https://api.github.com/repos/csthomas/joomla-cms/tags","blobs_url":"https://api.github.com/repos/csthomas/joomla-cms/git/blobs{/sha}","git_tags_url":"https://api.github.com/repos/csthomas/joomla-cms/git/tags{/sha}","git_refs_url":"https://api.github.com/repos/csthomas/joomla-cms/git/refs{/sha}","trees_url":"https://api.github.com/repos/csthomas/joomla-cms/git/trees{/sha}","statuses_url":"https://api.github.com/repos/csthomas/joomla-cms/statuses/{sha}","languages_url":"https://api.github.com/repos/csthomas/joomla-cms/languages","stargazers_url":"https://api.github.com/repos/csthomas/joomla-cms/stargazers","contributors_url":"https://api.github.com/repos/csthomas/joomla-cms/contributors","subscribers_url":"https://api.github.com/repos/csthomas/joomla-cms/subscribers","subscription_url":"https://api.github.com/repos/csthomas/joomla-cms/subscription","commits_url":"https://api.github.com/repos/csthomas/joomla-cms/commits{/sha}","git_commits_url":"https://api.github.com/repos/csthomas/joomla-cms/git/commits{/sha}","comments_url":"https://api.github.com/repos/csthomas/joomla-cms/comments{/number}","issue_comment_url":"https://api.github.com/repos/csthomas/joomla-cms/issues/comments{/number}","contents_url":"https://api.github.com/repos/csthomas/joomla-cms/contents/{+path}","compare_url":"https://api.github.com/repos/csthomas/joomla-cms/compare/{base}...{head}","merges_url":"https://api.github.com/repos/csthomas/joomla-cms/merges","archive_url":"https://api.github.com/repos/csthomas/joomla-cms/{archive_format}{/ref}","downloads_url":"https://api.github.com/repos/csthomas/joomla-cms/downloads","issues_url":"https://api.github.com/repos/csthomas/joomla-cms/issues{/number}","pulls_url":"https://api.github.com/repos/csthomas/joomla-cms/pulls{/number}","milestones_url":"https://api.github.com/repos/csthomas/joomla-cms/milestones{/number}","notifications_url":"https://api.github.com/repos/csthomas/joomla-cms/notifications{?since,all,participating}","labels_url":"https://api.github.com/repos/csthomas/joomla-cms/labels{/name}","releases_url":"https://api.github.com/repos/csthomas/joomla-cms/releases{/id}","deployments_url":"https://api.github.com/repos/csthomas/joomla-cms/deployments","created_at":"2015-11-02T08:28:28Z","updated_at":"2016-01-13T19:36:09Z","pushed_at":"2016-11-17T21:39:44Z","git_url":"git://github.com/csthomas/joomla-cms.git","ssh_url":"git@github.com:csthomas/joomla-cms.git","clone_url":"https://github.com/csthomas/joomla-cms.git","svn_url":"https://github.com/csthomas/joomla-cms","homepage":"https://www.joomla.org","size":164187,"stargazers_count":0,"watchers_count":0,"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":0,"default_branch":"staging"}},"base":{"label":"joomla:staging","ref":"staging","sha":"123821ae126c0b4f8a75a581acdb88a790f40c2f","user":{"login":"joomla","id":751633,"avatar_url":"https://avatars.githubusercontent.com/u/751633?v=3","gravatar_id":"","url":"https://api.github.com/users/joomla","html_url":"https://github.com/joomla","followers_url":"https://api.github.com/users/joomla/followers","following_url":"https://api.github.com/users/joomla/following{/other_user}","gists_url":"https://api.github.com/users/joomla/gists{/gist_id}","starred_url":"https://api.github.com/users/joomla/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/joomla/subscriptions","organizations_url":"https://api.github.com/users/joomla/orgs","repos_url":"https://api.github.com/users/joomla/repos","events_url":"https://api.github.com/users/joomla/events{/privacy}","received_events_url":"https://api.github.com/users/joomla/received_events","type":"Organization","site_admin":false},"repo":{"id":2464908,"name":"joomla-cms","full_name":"joomla/joomla-cms","owner":{"login":"joomla","id":751633,"avatar_url":"https://avatars.githubusercontent.com/u/751633?v=3","gravatar_id":"","url":"https://api.github.com/users/joomla","html_url":"https://github.com/joomla","followers_url":"https://api.github.com/users/joomla/followers","following_url":"https://api.github.com/users/joomla/following{/other_user}","gists_url":"https://api.github.com/users/joomla/gists{/gist_id}","starred_url":"https://api.github.com/users/joomla/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/joomla/subscriptions","organizations_url":"https://api.github.com/users/joomla/orgs","repos_url":"https://api.github.com/users/joomla/repos","events_url":"https://api.github.com/users/joomla/events{/privacy}","received_events_url":"https://api.github.com/users/joomla/received_events","type":"Organization","site_admin":false},"private":false,"html_url":"https://github.com/joomla/joomla-cms","description":"Home of the Joomla! Content Management System","fork":false,"url":"https://api.github.com/repos/joomla/joomla-cms","forks_url":"https://api.github.com/repos/joomla/joomla-cms/forks","keys_url":"https://api.github.com/repos/joomla/joomla-cms/keys{/key_id}","collaborators_url":"https://api.github.com/repos/joomla/joomla-cms/collaborators{/collaborator}","teams_url":"https://api.github.com/repos/joomla/joomla-cms/teams","hooks_url":"https://api.github.com/repos/joomla/joomla-cms/hooks","issue_events_url":"https://api.github.com/repos/joomla/joomla-cms/issues/events{/number}","events_url":"https://api.github.com/repos/joomla/joomla-cms/events","assignees_url":"https://api.github.com/repos/joomla/joomla-cms/assignees{/user}","branches_url":"https://api.github.com/repos/joomla/joomla-cms/branches{/branch}","tags_url":"https://api.github.com/repos/joomla/joomla-cms/tags","blobs_url":"https://api.github.com/repos/joomla/joomla-cms/git/blobs{/sha}","git_tags_url":"https://api.github.com/repos/joomla/joomla-cms/git/tags{/sha}","git_refs_url":"https://api.github.com/repos/joomla/joomla-cms/git/refs{/sha}","trees_url":"https://api.github.com/repos/joomla/joomla-cms/git/trees{/sha}","statuses_url":"https://api.github.com/repos/joomla/joomla-cms/statuses/{sha}","languages_url":"https://api.github.com/repos/joomla/joomla-cms/languages","stargazers_url":"https://api.github.com/repos/joomla/joomla-cms/stargazers","contributors_url":"https://api.github.com/repos/joomla/joomla-cms/contributors","subscribers_url":"https://api.github.com/repos/joomla/joomla-cms/subscribers","subscription_url":"https://api.github.com/repos/joomla/joomla-cms/subscription","commits_url":"https://api.github.com/repos/joomla/joomla-cms/commits{/sha}","git_commits_url":"https://api.github.com/repos/joomla/joomla-cms/git/commits{/sha}","comments_url":"https://api.github.com/repos/joomla/joomla-cms/comments{/number}","issue_comment_url":"https://api.github.com/repos/joomla/joomla-cms/issues/comments{/number}","contents_url":"https://api.github.com/repos/joomla/joomla-cms/contents/{+path}","compare_url":"https://api.github.com/repos/joomla/joomla-cms/compare/{base}...{head}","merges_url":"https://api.github.com/repos/joomla/joomla-cms/merges","archive_url":"https://api.github.com/repos/joomla/joomla-cms/{archive_format}{/ref}","downloads_url":"https://api.github.com/repos/joomla/joomla-cms/downloads","issues_url":"https://api.github.com/repos/joomla/joomla-cms/issues{/number}","pulls_url":"https://api.github.com/repos/joomla/joomla-cms/pulls{/number}","milestones_url":"https://api.github.com/repos/joomla/joomla-cms/milestones{/number}","notifications_url":"https://api.github.com/repos/joomla/joomla-cms/notifications{?since,all,participating}","labels_url":"https://api.github.com/repos/joomla/joomla-cms/labels{/name}","releases_url":"https://api.github.com/repos/joomla/joomla-cms/releases{/id}","deployments_url":"https://api.github.com/repos/joomla/joomla-cms/deployments","created_at":"2011-09-27T02:07:38Z","updated_at":"2016-11-17T18:20:50Z","pushed_at":"2016-11-17T21:39:48Z","git_url":"git://github.com/joomla/joomla-cms.git","ssh_url":"git@github.com:joomla/joomla-cms.git","clone_url":"https://github.com/joomla/joomla-cms.git","svn_url":"https://github.com/joomla/joomla-cms","homepage":"https://www.joomla.org","size":309728,"stargazers_count":2189,"watchers_count":2189,"language":"PHP","has_issues":true,"has_downloads":true,"has_wiki":true,"has_pages":false,"forks_count":2334,"mirror_url":null,"open_issues_count":563,"forks":2334,"open_issues":563,"watchers":2189,"default_branch":"staging"}},"_links":{"self":{"href":"https://api.github.com/repos/joomla/joomla-cms/pulls/12839"},"html":{"href":"https://github.com/joomla/joomla-cms/pull/12839"},"issue":{"href":"https://api.github.com/repos/joomla/joomla-cms/issues/12839"},"comments":{"href":"https://api.github.com/repos/joomla/joomla-cms/issues/12839/comments"},"review_comments":{"href":"https://api.github.com/repos/joomla/joomla-cms/pulls/12839/comments"},"review_comment":{"href":"https://api.github.com/repos/joomla/joomla-cms/pulls/comments{/number}"},"commits":{"href":"https://api.github.com/repos/joomla/joomla-cms/pulls/12839/commits"},"statuses":{"href":"https://api.github.com/repos/joomla/joomla-cms/statuses/edb8c3b1e8082eb31e5cee9438e0d3dbc7b967ff"}}}}
{"actor":{"display_login":"csthomas"}}