| Index: trunk/extensions/ArticleFeedbackv5/sql/ArticleFeedbackv5.sql |
| — | — | @@ -15,7 +15,7 @@ |
| 16 | 16 | -- Stores feedback records: "user X submitted feedback on page Y, at time Z" |
| 17 | 17 | CREATE TABLE IF NOT EXISTS /*_*/aft_article_feedback ( |
| 18 | 18 | -- Row ID (primary key) |
| 19 | | - af_id integer unsigned NOT NULL PRIMARY KEY AUTO_INCREMENT, |
| | 19 | + af_id integer unsigned NOT NULL PRIMARY KEY AUTO_INCREMENT, |
| 20 | 20 | -- Foreign key to page.page_id |
| 21 | 21 | af_page_id integer unsigned NOT NULL, |
| 22 | 22 | -- User Id (0 if anon), and ip address |
| — | — | @@ -39,6 +39,8 @@ |
| 40 | 40 | af_abuse_count integer unsigned NOT NULL DEFAULT 0, |
| 41 | 41 | af_helpful_count integer unsigned NOT NULL DEFAULT 0, |
| 42 | 42 | af_unhelpful_count integer unsigned NOT NULL DEFAULT 0, |
| | 43 | + -- Net helpfulness (helpful - unhelpful). Used in fetch query. |
| | 44 | + af_net_helpfulness integer NOT NULL DEFAULT 0, |
| 43 | 45 | -- Flag a message as requiring oversight, being hidden ,or being deleted |
| 44 | 46 | af_needs_oversight boolean NOT NULL DEFAULT FALSE, |
| 45 | 47 | af_is_deleted boolean NOT NULL DEFAULT FALSE, |
| Index: trunk/extensions/ArticleFeedbackv5/sql/wmfupdate-jan31.sql |
| — | — | @@ -11,6 +11,9 @@ |
| 12 | 12 | ADD COLUMN af_unhelpful_count integer unsigned NOT NULL DEFAULT 0, |
| 13 | 13 | ADD COLUMN af_needs_oversight boolean NOT NULL DEFAULT FALSE, |
| 14 | 14 | ADD COLUMN af_is_deleted boolean NOT NULL DEFAULT FALSE, |
| 15 | | - ADD COLUMN af_is_hidden boolean NOT NULL DEFAULT FALSE; |
| | 15 | + ADD COLUMN af_is_hidden boolean NOT NULL DEFAULT FALSE, |
| | 16 | + ADD COLUMN af_net_helpfulness integer NOT NULL DEFAULT 0; |
| 16 | 17 | |
| 17 | | -CREATE INDEX /*i*/afo_field_id ON /*_*/aft_article_field_option (afo_field_id); |
| \ No newline at end of file |
| | 18 | +CREATE INDEX /*i*/afo_field_id ON /*_*/aft_article_field_option (afo_field_id); |
| | 19 | +CREATE INDEX /*i*/af_net_helpfulness ON /*_*/aft_article_feedback (af_net_helpfulness); |
| | 20 | + |
| Index: trunk/extensions/ArticleFeedbackv5/sql/alter.sql |
| — | — | @@ -117,3 +117,8 @@ |
| 118 | 118 | |
| 119 | 119 | -- Added 1/31 (Roan) |
| 120 | 120 | CREATE INDEX /*i*/afo_field_id ON /*_*/aft_article_field_option (afo_field_id); |
| | 121 | + |
| | 122 | +-- Added 1/31 (greg) |
| | 123 | +ALTER TABLE /*_*/aft_article_feedback ADD COLUMN af_net_helpfulness integer NOT NULL DEFAULT 0; |
| | 124 | +CREATE INDEX /*i*/af_net_helpfulness ON /*_*/aft_article_feedback (af_net_helpfulness); |
| | 125 | +UPDATE aft_article_feedback SET af_net_helpfulness = CONVERT(af_helpful_count, SIGNED) - CONVERT(af_unhelpful_count, SIGNED); |
| Index: trunk/extensions/ArticleFeedbackv5/api/ApiFlagFeedbackArticleFeedbackv5.php |
| — | — | @@ -171,6 +171,16 @@ |
| 172 | 172 | $results['helpful'] = wfMessage( 'articlefeedbackv5-form-helpful-votes', |
| 173 | 173 | $helpful, $unhelpful |
| 174 | 174 | )->escaped(); |
| | 175 | + |
| | 176 | + // Update net_helpfulness after flagging as helpful/unhelpful. |
| | 177 | + $dbw->update( |
| | 178 | + 'aft_article_feedback', |
| | 179 | + array( 'af_net_helpfulness = CONVERT(af_helpful_count, SIGNED) - CONVERT(af_unhelpful_count, SIGNED)' ), |
| | 180 | + array( |
| | 181 | + 'af_id' => $params['feedbackid'], |
| | 182 | + ), |
| | 183 | + __METHOD__ |
| | 184 | + ); |
| 175 | 185 | } |
| 176 | 186 | |
| 177 | 187 | // Conditional formatting for abuse flag |
| Index: trunk/extensions/ArticleFeedbackv5/api/ApiViewFeedbackArticleFeedbackv5.php |
| — | — | @@ -103,9 +103,7 @@ |
| 104 | 104 | // Build ORDER BY clause. |
| 105 | 105 | switch( $sort ) { |
| 106 | 106 | case 'helpful': |
| 107 | | - $sortField = 'net_helpfulness'; |
| 108 | | - // Can't use aliases in mysql where clauses. |
| 109 | | - $continueSql = "CONVERT(af_helpful_count, SIGNED) - CONVERT(af_unhelpful_count, SIGNED) $continueDirection"; |
| | 107 | + $sortField = 'af_net_helpfulness'; |
| 110 | 108 | break; |
| 111 | 109 | case 'rating': |
| 112 | 110 | $sortField = 'rating'; |
| — | — | @@ -114,10 +112,10 @@ |
| 115 | 113 | # Default field, fall through |
| 116 | 114 | default: |
| 117 | 115 | $sortField = 'af_id'; |
| 118 | | - $continueSql = "$sortField $continueDirection"; |
| 119 | 116 | break; |
| 120 | 117 | } |
| 121 | | - $order = "$sortField $direction"; |
| | 118 | + $order = "$sortField $direction"; |
| | 119 | + $continueSql = "$sortField $continueDirection"; |
| 122 | 120 | |
| 123 | 121 | // Build WHERE clause. |
| 124 | 122 | // Filter applied , if any: |
| — | — | @@ -145,7 +143,7 @@ |
| 146 | 144 | ), |
| 147 | 145 | array( |
| 148 | 146 | 'af_id', |
| 149 | | - 'CONVERT(af_helpful_count, SIGNED) - CONVERT(af_unhelpful_count, SIGNED) AS net_helpfulness', |
| | 147 | + 'af_net_helpfulness', |
| 150 | 148 | 'rating.aa_response_boolean AS rating' |
| 151 | 149 | ), |
| 152 | 150 | $where, |
| — | — | @@ -189,8 +187,8 @@ |
| 190 | 188 | 'af_helpful_count', 'af_unhelpful_count', |
| 191 | 189 | 'af_is_deleted', 'af_needs_oversight', |
| 192 | 190 | '(SELECT COUNT(*) FROM ' . $dbr->tableName( 'revision' ) . ' WHERE rev_id > af_revision_id AND rev_page = ' . ( integer ) $pageId . ') AS age', |
| 193 | | - 'CONVERT(af_helpful_count, SIGNED) - CONVERT(af_unhelpful_count, SIGNED) AS net_helpfulness', |
| 194 | | - 'page_latest', 'af_revision_id', 'page_title', 'page_namespace', |
| | 191 | + 'af_net_helpfulness', 'af_revision_id', |
| | 192 | + 'page_latest', 'page_title', 'page_namespace', |
| 195 | 193 | 'rating.aa_response_boolean AS rating' |
| 196 | 194 | ), |
| 197 | 195 | array( 'af_id' => $ids ), |