Databases
StackOverflow2013
Postsdbo
PO
 

Note that there are some explanatory texts on larger screens.

plurals
  1. PO
    primarykey
    data
    text
    <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>
    singulars
    1. This table or related slice is empty.
    1. PORanking Database Entries
      singulars
      1. PTQuestion
    1. PTAnswer
    1. This table or related slice is empty.
    1. USnavige
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. COThanks for the comment, but here's the problem. Images are selected at random. A user might get image A 100 times, image B 10 times, and image C 1 time. The user has indicated that he likes A above B every time they have been compared. Then C pops up with A and the user says he liked C better than A. Well A has 100 votes and C only has 1, but C's vote was higher than A, so in the rankings C should be higher. This is the problem. Of course instead of being random I could have images be displayed as evenly as possible...
      singulars
      1. PO
      1. USJonathan Plumb
    2. COWell the question is how you want to define your ranking function. Consider the function I put above you would get the following ranking for your example: C > A > B (C at first place because it has 1 like and no dislike, then A because it has 100 likes but 1 dislike, too, then B). If you, however, rather wish to have something like A > C > B, because A has been liked generally more often than C, then you could use a function like t1.c_liked - t2.c_disliked. It is really up to how complicated you want to make the ranking function.
      singulars
      1. PO
      1. USnavige
    3. COI think I may have solved it. I'm just going to give each item a number value. If the two items have the same value, the one you vote for gets +1. If the one you voted for was less than the one you didn't, it's value goes to +1 of the one you didn't vote for. Would this work or is it flawed?
      singulars
      1. PO
      1. USJonathan Plumb
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

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