{"action":"created","issue":{"url":"https://api.github.com/repos/cabrerahector/wordpress-popular-posts/issues/104","repository_url":"https://api.github.com/repos/cabrerahector/wordpress-popular-posts","labels_url":"https://api.github.com/repos/cabrerahector/wordpress-popular-posts/issues/104/labels{/name}","comments_url":"https://api.github.com/repos/cabrerahector/wordpress-popular-posts/issues/104/comments","events_url":"https://api.github.com/repos/cabrerahector/wordpress-popular-posts/issues/104/events","html_url":"https://github.com/cabrerahector/wordpress-popular-posts/issues/104","id":139360504,"number":104,"title":"only weekly and monthly caused unexpected results.","user":{"login":"keisukemizuno","id":1429563,"avatar_url":"https://avatars.githubusercontent.com/u/1429563?v=3","gravatar_id":"","url":"https://api.github.com/users/keisukemizuno","html_url":"https://github.com/keisukemizuno","followers_url":"https://api.github.com/users/keisukemizuno/followers","following_url":"https://api.github.com/users/keisukemizuno/following{/other_user}","gists_url":"https://api.github.com/users/keisukemizuno/gists{/gist_id}","starred_url":"https://api.github.com/users/keisukemizuno/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/keisukemizuno/subscriptions","organizations_url":"https://api.github.com/users/keisukemizuno/orgs","repos_url":"https://api.github.com/users/keisukemizuno/repos","events_url":"https://api.github.com/users/keisukemizuno/events{/privacy}","received_events_url":"https://api.github.com/users/keisukemizuno/received_events","type":"User","site_admin":false},"labels":[],"state":"open","locked":false,"assignee":{"login":"cabrerahector","id":3186556,"avatar_url":"https://avatars.githubusercontent.com/u/3186556?v=3","gravatar_id":"","url":"https://api.github.com/users/cabrerahector","html_url":"https://github.com/cabrerahector","followers_url":"https://api.github.com/users/cabrerahector/followers","following_url":"https://api.github.com/users/cabrerahector/following{/other_user}","gists_url":"https://api.github.com/users/cabrerahector/gists{/gist_id}","starred_url":"https://api.github.com/users/cabrerahector/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/cabrerahector/subscriptions","organizations_url":"https://api.github.com/users/cabrerahector/orgs","repos_url":"https://api.github.com/users/cabrerahector/repos","events_url":"https://api.github.com/users/cabrerahector/events{/privacy}","received_events_url":"https://api.github.com/users/cabrerahector/received_events","type":"User","site_admin":false},"milestone":null,"comments":5,"created_at":"2016-03-08T18:48:51Z","updated_at":"2016-03-11T08:25:44Z","closed_at":null,"body":"Recently,\r\nonly 'weekly' and 'monthly' options caused unexpected results.\r\n\r\n```\r\nSELECT p.ID AS 'id', p.post_title AS 'title', p.post_date AS 'date', p.post_author AS 'uid', v.pageviews AS 'pageviews'\r\nFROM (SELECT postid, IFNULL(SUM(pageviews), 0) AS pageviews\r\nFROM wp_popularpostssummary\r\nWHERE last_viewed > DATE_SUB('2016-03-09 02:55:42', INTERVAL 1 DAY)\r\nGROUP BY postid\r\nORDER BY pageviews DESC) v\r\nLEFT JOIN wp_posts p\r\nON v.postid = p.ID\r\nWHERE 1 = 1\r\nAND p.post_type = 'post'\r\nAND p.post_password = ''\r\nAND p.post_status = 'publish'\r\nLIMIT 10;\r\n```\r\n\r\nThis query will return the expected results.\r\nThe results is ordered by 'pageviews DESC'.\r\nBut the following query will return the results ordered by 'id DESC'.\r\n\r\n```\r\nSELECT p.ID AS 'id', p.post_title AS 'title', p.post_date AS 'date', p.post_author AS 'uid', v.pageviews AS 'pageviews'\r\nFROM (SELECT postid, IFNULL(SUM(pageviews), 0) AS pageviews\r\nFROM wp_popularpostssummary\r\nWHERE last_viewed > DATE_SUB('2016-03-09 02:55:42', INTERVAL 1 WEEK)\r\nGROUP BY postid\r\nORDER BY pageviews DESC) v\r\nLEFT JOIN wp_posts p\r\nON v.postid = p.ID\r\nWHERE 1 = 1\r\nAND p.post_type = 'post'\r\nAND p.post_password = ''\r\nAND p.post_status = 'publish'\r\nLIMIT 10;\r\n```\r\n\r\n'INTERVAL 1 MONTH' is alse same.\r\nI tried to examine.\r\n\r\n```\r\nSELECT postid, IFNULL(SUM(pageviews), 0) AS pageviews\r\nFROM wp_popularpostssummary\r\nWHERE last_viewed > DATE_SUB('2016-03-09 02:55:42', INTERVAL 1 WEEK)\r\nGROUP BY postid\r\nORDER BY pageviews DESC\r\n```\r\n\r\nThe above query will return the expected results.\r\nThe results is ordered by 'pageviews DESC'.\r\nBut,\r\n\r\n```\r\nWHERE 1 = 1\r\nAND p.post_type = 'post'\r\nAND p.post_password = ''\r\nAND p.post_status = 'publish'\r\n```\r\nI don't know why, but\r\nby adding the above query, it will return the results ordered by 'id DESC'.\r\n\r\nOn the other hand,\r\n\r\n```\r\nWHERE 1 = 1\r\nAND p.post_type = 'post'\r\nAND p.post_password = ''\r\n```\r\n\r\nIn this case, it will return the results ordered by 'pageviews DESC'.\r\n\r\n\r\nOr, the following query will return the results ordered by 'pageviews DESC'.\r\n\r\n```\r\nSELECT p.ID AS 'id', p.post_title AS 'title', p.post_date AS 'date', p.post_author AS 'uid', v.pageviews AS 'pageviews'\r\nFROM (SELECT postid, IFNULL(SUM(pageviews), 0) AS pageviews\r\nFROM wp_popularpostssummary\r\nWHERE last_viewed > DATE_SUB('2016-03-09 02:55:42', INTERVAL 1 WEEK)\r\nGROUP BY postid\r\nORDER BY pageviews DESC) v\r\nLEFT JOIN wp_posts p\r\nON v.postid = p.ID\r\nWHERE 1 = 1\r\nAND p.post_type = 'post'\r\nAND p.post_password = ''\r\nAND p.post_status = 'publish'\r\nORDER BY pageviews DESC\r\nLIMIT 10;\r\n```\r\n\r\n\r\n\r\nMaybe, around Jan 2016,\r\nboth 'weekly option' and 'monthly option' could have return the results ordered by 'pageviews DESC'.\r\n\r\nIf you know anything, please tell."},"comment":{"url":"https://api.github.com/repos/cabrerahector/wordpress-popular-posts/issues/comments/195254710","html_url":"https://github.com/cabrerahector/wordpress-popular-posts/issues/104#issuecomment-195254710","issue_url":"https://api.github.com/repos/cabrerahector/wordpress-popular-posts/issues/104","id":195254710,"user":{"login":"keisukemizuno","id":1429563,"avatar_url":"https://avatars.githubusercontent.com/u/1429563?v=3","gravatar_id":"","url":"https://api.github.com/users/keisukemizuno","html_url":"https://github.com/keisukemizuno","followers_url":"https://api.github.com/users/keisukemizuno/followers","following_url":"https://api.github.com/users/keisukemizuno/following{/other_user}","gists_url":"https://api.github.com/users/keisukemizuno/gists{/gist_id}","starred_url":"https://api.github.com/users/keisukemizuno/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/keisukemizuno/subscriptions","organizations_url":"https://api.github.com/users/keisukemizuno/orgs","repos_url":"https://api.github.com/users/keisukemizuno/repos","events_url":"https://api.github.com/users/keisukemizuno/events{/privacy}","received_events_url":"https://api.github.com/users/keisukemizuno/received_events","type":"User","site_admin":false},"created_at":"2016-03-11T08:25:44Z","updated_at":"2016-03-11T08:25:44Z","body":"Thank you for your time.\r\nPlease contact me anytime!"}}