Voting/Ranking logic with SQL

Question

I'm working on a project that will use a comparison voting logic to sort the highest rated to the top and lowest to the bottom(Similar to a "hot or not" or "Hotstagram"). Basically what I need to do is take 2 random pictures that are directly next to each other in the database and have users vote, adding one point to the winner and subtracting on point from the loser essentially filtering the highest to the top.

My question is two fold, How can I get a random item in a MySql database then get a random item directly next to it. and secondly (if anyone has prior experience with this) how would you structure your DB? I'm thinking One table for images and a second that will hold the votes(then compile the results on page load). I guess what my concern is here, for the ranking, ever new entered photo will start at zero, so you could have X amount of photos with the same rank? I'm just throwing some thoughts out there and I need another mind to toss this around.

Fluffeh · Accepted Answer · 2012-07-22T14:23:52.307

You can use the Order by Rand() function to get a random record from the database.

SELECT * FROM tbl_name ORDER BY RAND();

You can also limit this to two rows easily enough as such:

SELECT * FROM tbl_name ORDER BY RAND() limit 2;

This will however get two random rows from the db, not two consecutive rows.

If you wanted to get two random AND consecutive rows, you could run a query to get the ID and a subquery to get the next ID after it.

As for keep data in one/two tables, definately keep it in two tables. Link your second table that keeps the votes with a link to the ID of the image. Obviously add indexes as required on the linked (and or other fields) as needed.

Edit: Here is the code to get you the two subsequent rows of data in one row then:

select
    a.id,
    (
        select
            min(c.id)
        from
            table1 c
        where
            c.id>a.id
        limit 1
    ) as id2
from
    table1 a
order by
    rand()
limit 1;

Edit 2: If you wanted them as separate rows to pull in all sorts of other bits, I have provided the query below. I used an extra subquery as if the initial query happened to pull out the max value (possible when using order by rand() then it solves the issue of only returning one row of data.

select
    b.id
from
    table1 b,
    (
    select
        a.id as id
    from
        table1 a
    where
        a.id<(select max(id) from table1 limit 1)
    order by 
        rand() 
    limit 1
    ) a
where
    b.id>=a.id
    order by
    b.id
limit 2
;

Yeah it appears that subqueries are going to be the only option unfortunately. — G.Thompson, Jul 22 '12 at 13:54
@GThompson Okay, I added the query now to return the two consecutive records for you. — Fluffeh, Jul 22 '12 at 14:02
@GThompson I also added a query to pull the two consecutive records in different rows and included a subquery to always make sure you get two rows of data when getting the random rows out. — Fluffeh, Jul 22 '12 at 14:25
Can I ask you one more question? what if I was keeping track of previously pulled queries and tried to prevent them from showing up. Say I had pulled 6 and 7 is there anyway to prevent that from the next queries? — G.Thompson, Jul 23 '12 at 02:35
@GThompson Yes, it is possible, but it certainly ISN'T a simple thing to do. Basically, you need to track how many times each one is shown, implement an algorithm of some sort to increase the views of low-hit IDs and so on. To get an idea of something like this, try searching for open source ad-rotation code. They basically implement this all through it. — Fluffeh, Jul 23 '12 at 02:53

score 1 · Answer 2 · answered Jul 22 '12 at 14:36

Here is another way to get two random rows in order:

with first as (select id
               from table t
               where id < (select max(id) from table)
               order by rand()
               limit 1
              )
select id
from table t
order by abs(id - first.id)
limit 2

As for structuring the data. You will probably want one for the photos, one for users, one for the "offers" (pairs of photos with a user id), and one for the votes. It usually becomes quite important to know what users are not voting for as well as what they are voting for.

If you want to break ties on photos with the same ranking, you can use the date the photo first entered the system, the number of offers, or some combination of the two.

Voting/Ranking logic with SQL

2 Answers2