Order database results by bayesian rating

0

Please login or register to vote for this query.

(click on this box to dismiss)

Wordpress database posts,postmeta, bayesian order

Drupal Answers Meta

Q&A about the site for Drupal developers and administrators

-- Enter Query Title
-- Enter Query Description

CREATE TABLE #posts(
  ID  int,
  title varchar(30)
);

CREATE TABLE #postmeta (
  post_id  int,
  meta_key varchar(30),
  meta_value real
);

INSERT INTO #posts VALUES 
(1, 'Title 1'),
(2, 'Title 2'),
(3, 'Title 3'),
(4, 'Title 4'),
(5, 'Title 5');

INSERT INTO #postmeta  VALUES
(1,'this_num_votes', 123),
(2,'this_num_votes', 1612),
(3,'this_num_votes', 2131),
(4,'this_num_votes', 7512),
(5,'this_num_votes', 3131),
(1,'this_rating', 0.54),
(2,'this_rating', 0.99),
(3,'this_rating', 0.75),
(4,'this_rating', 0.23),
(5,'this_rating', 0.37);



SELECT id,title,( AVG(this_num_votes) * AVG(this_rating) + this_num_votes * this_rating ) 
     / ( AVG(this_num_votes) + this_num_votes ) as br
FROM #posts
LEFT JOIN (
SELECT DISTINCT post_id,
(SELECT meta_value FROM #postmeta WHERE #postmeta.post_id = pm.post_id AND meta_key ='this_num_votes') as this_num_votes,
(SELECT meta_value FROM #postmeta WHERE #postmeta.post_id = pm.post_id AND meta_key ='this_rating') as this_rating
FROM #postmeta pm ) as newmeta ON #posts.ID = newmeta.post_id
GROUP BY id,title,this_num_votes,this_rating
ORDER BY br DESC

Enter Parameters

Options:
Switch to main site
loading Hold tight while we fetch your results