Improvement Poll Method on Wikirating

Dear all

On [url removed, login to view] the poll method for credit ratings (see [url removed, login to view]) considers all user ratings ever given. The data (user, rating, rated object, timestamp, IP address) are stored in a MySQL table. The poll method extension practically uses a SQL query to evaluate the average rating of each object (that is a country or a company):

SELECT AVG(rating) FROM credit_rating_poll_<object> WHERE page_id='<country>'

Note: In any event, at least a certain number of user votes are needed, to evaluate an average rating (at the moment 10 are needed). Now this SQL query should be changed/improved as follows:


x = minimum needed user votes (for each object)

y = timeframe within user votes are considered (for each object)

Only all user ratings within a timeframe y should be evaluated (not all like now) - with the following conditions:

1. If the total number of user ratings are smaller than x, than extend the timeframe y so much, that the needed x are reached

2. If all user ratings are anyway smaller than the needed x, than extend the timeframe y to "infinity" (that is, no limitation for the timeframe, unless the needed x is reached)

It is important, that the new SQL query should remain "one nested query". Maybe than it is necessary to add to the MySQL table some more columns - but this is not a problem (I presume). A (non working) example, how the new SQL qurey could be (which I tried):

SELECT avg(rating) average, page_id, count([url removed, login to view]) FROM (SELECT [url removed, login to view] FROM credit_rating_poll_<object> AS t WHERE t.time>=0) x GROUP BY page_id HAVING count(*)>=10 ORDER BY page_id ASC LIMIT 100

How much time would this changement take?

Best, Dorian

Evner: MySQL, PHP

Se mere: ratings country, rating by country, asc it, asc company, poll, method, nested sql, sql nested query, nested query sql, object method, nested query, event method, non rated, needed 100 votes, mysql nested data, poll votes, extend table mysql, mysql table count, user poll example, best sql query, mysql select columns, mysql rating rating count, mysql columns count, select query event, poll f

Om arbejdsgiveren:
( 0 bedømmelser ) Zurich, Switzerland

Projekt ID: #4065932

1 freelancer is bidding on average $15 for this job


Hired by the Employer

$15 USD / time
(263 bedømmelser)