Welcome back for day nine of the 12 Days of Retool! Last Friday, we showed how to use the Retool IFrame component to embed classic video games in your browser. If you missed that post, or any other post during the 12 Days of Retool, a full list can be found at the bottom of this page.

Today, we'll show how to use the List View component, Table component, and Retool DB to build an application that will help fans rate Star Wars Episodes 1-9, and share their analysis with the rest of the Internet. You can see the live application here, and import a version of this application to play with yourself using this app JSON file. Rank your Star Wars movie preferences using the buttons next to each movie, and click "Save Rank" to register your preference for the community tally on the right!

In an unprecedented move, someone has shared opinions about Star Wars on the Internet.

Read on to learn how we built this application.

The application state awakens

This application uses a List View component to render a list of all nine movies in the core Star Wars series. There are two temporary state variables used by this application - one to contain actual data about each movie, and one to contain the user's configured order for each movie.

The static data for the Star Wars movies lives in this state variable.

When the page loads, we execute a JavaScript query that will initialize our application. In order to have a JavaScript query execute on page load, check this box under the "Advanced" tab for the query.

This is a bit like window.onload for a Retool application.

Inside that query, we will generate a unique identifier for the user in this browser, and then kick off two queries that will:

  • Check for and load a user's stored movie order preference
  • Execute a query to analyze all the stored movie orders in our database across all users
let userId = localStorage?.values?.starWarsMovieOrderId;

if (!userId) {
  // If this is a new user, set up an ID and allow the default rankings
  userId = uuid.v4();
  localStorage.setValue('starWarsMovieOrderId', userId);
}

loadPreference.trigger();
communityPreferences.trigger();

Attack of the SQL queries

To power this application, we need to use three SQL queries against a database with the star_wars_preferences table created, with at least two text fields for movie_order and client_id. In this example I used Retool DB, but any PostgreSQL database should work.

Storing preferences is all too easy

To save the user's movie order preference, we use the GUI mode of a SQL query in Retool to do an update or insert operation.

While not as secure as freezing in carbonite, a Postgres database is one of the best places to store something for safekeeping.

Loading preferences like clone troopers on a gunship

During page load, we grab the user's saved preferences using this SQL query. The client_id field is the randomly generated ID we create for each new visitor to the application, and save in local storage. We use the {{ }} data binding syntax to insert this ID into the query.

select * 
	from star_wars_preferences 
    where client_id = {{ localStorage.values.starWarsMovieOrderId }};

Analyzing community rankings from the Jedi archives

During page load (and whenever a new ranking is saved), we query our database for the most recent movie rankings, grouped by unique order. Here's the query we use to produce this information, which is then bound to the Table component on the righthand side of the UI. The results are ordered from most popular to least popular rank order.

select movie_order, count(id) 
	from star_wars_preferences 
    group by movie_order 
    order by count(id);

I don't like sand, but I love event handlers

In the List View component that displays the movie rankings, each entry in the list has three buttons associated with it - a button to move the movie to the top of the list, and two buttons to move one of the films up or down based on your preference. Changing the ranking of a movie involves manipulating an array which holds the user's ordering preference after a button click. For example, the following script is executed when a user tries to move one of the films directly to the top of the list.

if (i === 0) return;

const newOrder = [...movieOrder.value];
const currentIndex = i;

const currentItem = newOrder[currentIndex];
newOrder.splice(currentIndex, 1);
newOrder.unshift(currentItem);

// The user's movie order is stored in this temp. state variable
movieOrder.setValue(newOrder);

The function calls back

The last interesting piece of this demo is a data table which displays community film rankings. The second column lists out all the film titles in order, and relies on a self-calling function to execute complex logic to render each row.

The code inside the double curly braces is a self-calling function, allowing us to have more complex logic than usual inside this JavaScript statement.

(function() {
  const ids = self.split(',');
  const mdata = movieData.value;
  let display = '';
  
  ids.forEach(id => {
    display += mdata[id].title + '\n';
  });

  return display;
})()

The final result looks like this.

Yub yub!

It's time to celebrate! You just walked through the most important parts of a simple Retool app that used List Views, tables, JavaScript, and SQL queries to rank all nine Star Wars films. Be sure to check the blog tomorrow when we share 10 great Retool video tutorials.

🔥
Appendix: Correct Star Wars Movie Rankings
If you are wondering what the correct ranking for the core Star Wars movies is, this is the order you are looking for:

Empire Strikes Back
A New Hope
Return of the Jedi
The Phantom Menace
Revenge of the Sith
Attack of the Clones
The Last Jedi
The Force Awakens
The Rise of Skywalker

The original trilogy is uncontroversially the best, with Empire leading the pack. The sequel trilogy is the worst, wasting an excellent cast and lead actor with a disjointed story arc across three films featuring reheated characters/storylines and a bizarre romantic subplot. The prequel trilogy, despite poor screenwriting and some terrible performances, still featured memorable music and set pieces, like the pod race and Darth Maul duel that contribute to making The Phantom Menace the best of the trio.

All 12 Days of Retool posts