0

My situations is this... I have a table of opportunities that is sorted. We have a paid service that will allow people to view the opportunities on the website any time. However we want an unpaid view that will show a random %/# of opportunities, which will always be the same. The opportunities are sorted out by dates; e.g. they will expire and be removed from the list, and a new one should be on the free search. However the only problem is that they will always have to show the same opportunity. (For example, I can't just pick random rows because it will cycle through them if they keep refreshing, and likewise can't just take the ones about to expire or furthest form expiry because people still end up seeing the entire list.

My only solution thus far is to add an extra column to the table to mark that it is open display. Then to count them on display, and if we are missing rows then to randomly select a few more. Below is a mock up...

SELECT count(id) as total FROM opportunities WHERE display_status="open" LIMIT 1000;
...
while(total < requiredNumber) {
    UPDATE opportunities SET display_status="open" WHERE display_status="private" ORDER BY random() LIMIT (required-total);
}

Can anyone think of a better way to solve this problem, preferably one that does not leave me adding another column to the table, and possible conflicts if many people load the page at a single time. One final note as well, it can't be a random set number of them (e.g. pick one, skip a few, take the next).

Any thought/comments would be very helpful, Thanks.

Sh4d0wsPlyr
  • 948
  • 12
  • 28
  • Before anyone asks, 1000 is just a random number limit I put in (to show the kind of limit we might set on the number of opportunities). I am also aware that the syntax is incorrect. As mentioned, its just a mock up to give an idea. – Sh4d0wsPlyr Aug 01 '14 at 17:53
  • 'Sorted'? You mean in the sense of 'OK'? – Strawberry Aug 01 '14 at 18:06
  • Sorted more in the sense that it is properly formatted, and can be easily sorted. – Sh4d0wsPlyr Aug 01 '14 at 18:58

2 Answers2

2

One way to make sure that a user only sees the same set of random rows is to feed the random number generator a seed that is linked to that user (such as their user_id). That means every user gets a random ordering of rows but it's always the same random ordering for each user.

Your code would be something:

 SELECT ...
 FROM   ...
 WHERE  ...
 ORDER BY random(<user id>)
 LIMIT <however many>

Note: as Twelfth pointed out, as new rows are created, they will get new order values and may end up in your random selection.

comfortablydrei
  • 316
  • 1
  • 4
  • interesting Idea....will the random seed produce the same rows if the total number of rows in the table changes (ie more records being inserted means that seed produces a different random total)? – Twelfth Aug 01 '14 at 18:12
  • @Twelfth I hadn't thought of that. I'll add a note to my answer. – comfortablydrei Aug 01 '14 at 18:37
  • As reference, if I use the same random seed for everyone (as each customer should see the same 'random' list anyways), it looks like this might work for me. I'll test it out in development and get back with correct answer if it works. With your note, does that mean that if I add more rows that my random opportunities will change? E.g. if I have 10 rows, and I have rows 3, 5, and 10. If I add another row I might now get 3, 4, 7? Just as an example... – Sh4d0wsPlyr Aug 01 '14 at 19:02
  • @Sh4d0wsPlyr I wish I could test that but I don't have access to MySQL or sqlfiddle at work. Let us know what you find. – comfortablydrei Aug 01 '14 at 19:04
  • @Sh4d0wsPlyr - You'll have to test it, but I suspect the number of records changing will cause the random seed to bring back different records. – Twelfth Aug 01 '14 at 19:18
1

I'm the type that doesn't like to lose information...including what random rows someone got to see. However I do not like the modification of your existing table idea...

Create a second table as randon_rows or something to that extent to save the ID's of the user and the ID's of the random records they got to see. Inner join to the table whenever you need to find those same rows again. You can also put expirey dates and the sort in the table as well, so the user isn't perma stuck with the same 10 rows.

Twelfth
  • 7,070
  • 3
  • 26
  • 34
  • Currently going through some of my older questions, and I realized I never answered this. I ended up doing something similar to this idea however, so I will mark it as correct. – Sh4d0wsPlyr May 26 '15 at 18:54