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!
Read on to learn how we built this application.
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.
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.
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
1let userId = localStorage?.values?.starWarsMovieOrderId;
2
3if (!userId) {
4 // If this is a new user, set up an ID and allow the default rankings
5 userId = uuid.v4();
6 localStorage.setValue('starWarsMovieOrderId', userId);
7}
8
9loadPreference.trigger();
10communityPreferences.trigger();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.
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.
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.
1select *
2 from star_wars_preferences
3 where client_id = {{ localStorage.values.starWarsMovieOrderId }};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.
1select movie_order, count(id)
2 from star_wars_preferences
3 group by movie_order
4 order by count(id);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.
1if (i === 0) return;
2
3const newOrder = [...movieOrder.value];
4const currentIndex = i;
5
6const currentItem = newOrder[currentIndex];
7newOrder.splice(currentIndex, 1);
8newOrder.unshift(currentItem);
9
10// The user's movie order is stored in this temp. state variable
11movieOrder.setValue(newOrder);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.
1(function() {
2 const ids = self.split(',');
3 const mdata = movieData.value;
4 let display = '';
5
6 ids.forEach(id => {
7 display += mdata[id].title + '\n';
8 });
9
10 return display;
11})()The final result looks like this.
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.
- Day 1: A parser for an abstract syntax tree
- Day 2: Two tuple hacks
- Day 3: Three equal signs
- Day 4: Four CRUD operations
- Day 5: Five Sidebar Wins
- Day 6: Six Commands in a Palette
- Day 7: Seven Big Fish Swimming
- Day 8: Eight Bits of Gaming
- Day 9: Nine Star Wars Movies
- Day 10: Ten Retool Videos
- Day 11: Eleven Best FIFA World Cup Games
- Day 12: Twelve Lines of AI Poetry
Reader
