1

Say for example I have a table:

Table: Message
| data | likes | dislikes |

How can I efficiently find the row that has the highest number of likes and dislikes i.e. most controversial. So for the following example: 

{("Hello", 10, 5)
("Ola!", 5, 5)
("Ni Hao!", 2, 2)
("Wazzup!", 5, 7)}

"Hello" would be chosen.

Any help would be highly appreciated!

izb
  • 50,101
  • 39
  • 117
  • 168
GWTNoob
  • 51
  • 3
  • 1
    You might want to rethink your criterion for "most controversial": do you really want to rate ("foo",1,1) more controversial than ("bar",100,99)? – Gareth McCaughan Feb 17 '11 at 00:30
  • Hey thanks for noting that. I wasn't thinking properly. I edited the question... – GWTNoob Feb 17 '11 at 00:37

3 Answers3

2

GQL (assuming you're using the app engine datastore) won't be able to do a query on a calculation.

You would need to add another property to your model (eg called 'controversy'), and calculate it every time you change the number of likes or dislikes. Then you could do a simple query on that other property (ie in descending order, then fetch the first N records).

Saxon Druce
  • 17,406
  • 5
  • 50
  • 71
1

1) You might want to use some other metrics instead of abs(likes - dislikes). In this case, (0, 5) and (100, 105) will be equally controversial.
I think, likes*dislikes might work in your conditions.

2) I'm not sure about jdoql, but since you specified sql tag, in standard sql this can be done without sorting. Something like 

select * from Message 
    where likes*dislikes = (select max(likes*dislikes) from Message)
Nikita Rybak
  • 67,365
  • 22
  • 157
  • 181
  • Hey, thanks. Yup thats exactly what I was looking for. Thanks. I restated the question to better suit the word "controversial" lol. – GWTNoob Feb 17 '11 at 00:38
  • @AppEngineNoob You can execute this query in any relational database, just put your own metrics in (it appears you want `likes + dislikes`). – Nikita Rybak Feb 17 '11 at 00:41
0
select top 1 (likes+dislikes ) as sumOfLikesDislikes from Message 
order by sumOfLikesDislikes desc

As suggested by Nikita, you can use (likes*dislikes) as LikesTimesDislikes (for metrics) if you want.

You can even do both :

select top 1 (likes+dislikes ) as sumOfLikesDislikes, 
             (likes*dislikes ) as LikesTimesDislikes  
from Message 
order by sumOfLikesDislikes desc, LikesTimesDislikes   desc

(First by sum, then by metrics)

Etienne
  • 46
  • 4