StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

PO
text
Body
copied!<p><em>So you posted your question in the c# group. I want to give you, however, a solution that is implemented in the database, making it more independent of your program.</em></p> <p>What you probably want to do first is to get the number of times an image has been liked and disliked. This SQL statement should do that for you (if you are using a database supporting grouping sets it would probably be easier to write):</p> <pre><code>SELECT t1.liked_id as id, t1.c_liked, t2.c_disliked FROM (SELECT liked_id, COUNT(*) as c_liked FROM table GROUP BY liked_id) t1 LEFT JOIN (SELECT disliked_id, COUNT(*) c_disliked FROM table GROUP BY disliked_id) t2 ON t1.liked_id = t2.disliked_id </code></pre> <p>Then it's up to you what you do with the numbers. In the outermost SELECT-statement, you could put a very complicated function, e.g. you could choose to weigh the dislikes less than the likes. To give you an idea of a possible very simple function:</p> <pre><code>SELECT t1.liked_id as id, (t1.c_liked/(t1.c_liked + t2.c_disliked) - t2.c_disliked/(t1.c_liked + t2.c_disliked)) as score </code></pre> <p>This returns you values [-1, 1] (which you could normalize to [0, 1] if you like, but don't have to), which you then can sort as in this example:</p> <pre><code>SELECT t1.liked_id as id, (t1.c_liked/(t1.c_liked + t2.c_disliked) - t2.c_disliked/(t1.c_liked + t2.c_disliked)) as score FROM (SELECT liked_id, COUNT(*) as c_liked FROM table GROUP BY liked_id) t1 LEFT JOIN (SELECT disliked_id, COUNT(*) c_disliked FROM table GROUP BY disliked_id) t2 ON t1.liked_id = t2.disliked_id ORDER BY score </code></pre>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload