CODEWITHSUNDEEP
pythonsqlflaskflask-sqlalchemy
Joshua Merriman
Joshua Merriman

Reputation: 985

Best way to show a user random data from an SQL database?

I'm working on a web app in Python (Flask) that, essentially, shows the user information from a PostgreSQL database (via Flask-SQLAlchemy) in a random order, with each set of information being shown on one page. Hitting a Next button will direct the user to the next set of data by replacing all data on the page with new data, and so on.

My conundrum comes with making the presentation truly random - not showing the user the same information twice by remembering what they've seen and not showing them those already seen sets of data again.

The site has no user system, and the "already seen" sets of data should be forgotten when they close the tab/window or navigate away.

I should also add that I'm a total newbie to SQL in general.

What is the best way to do this?

Upvotes: 1

Views: 684

Answers (2)

Daniel Fairhead
Daniel Fairhead

Reputation: 1151

The easiest way is to do the random number generation in javascript at the client end...

Tell the client what the highest number row is, then the client page keeps track of which ids it has requested (just a simple js array). Then when the "request next random page" button is clicked, it generates a new random number less than the highest valid row id, and providing that the number isn't in its list of previously viewed items, it will send a request for that item.

This way, you (on the server) only have to have 2 database accessing views:

  1. main page (which gives the js, and the highest valid row id)
  2. display an item (by id)

You don't have any complex session tracking, and the user's browser is only having to keep track of a simple list of numbers, which even if they personally view several thousand different items is still only going to be a meg or two of memory.

For performance reasons, you can even pre-fetch the next item as soon as the current item loads, so that it displays instantly and loads the next one in the background while they're looking at it. (jQuery .load() is your friend :-) )

If you expect a large number of items to be removed from the database (so that the highest number is not helpful), then you can instead generate a list of random ids, send that, and then request them one at a time. Pre-generate the random list, as it were.

Hope this helps! :-)

Upvotes: 1

Mark Richman
Mark Richman

Reputation: 29710

You could stick the "already seen" data in a session cookie. Selecting random SQL data is explained here

Upvotes: 0

Related Questions