SuperNimbusKnowledge Base

Search & Filter Parameters

Search by Email Address

Our search button currently returns all the player records in the database, so we want to add functionality to search for specific players by either their username or email address.

We will add these new components within a new container as that will allow us to create a module in a later section.

Add a new container above the table. Click on the ‘Container title’ and change the text to ‘Search Bar’. Move the ‘Search’ button inside and expand the container to fill the space.

Add two ‘Text Input’ components inside the container. Make them both fill three columns and change their labels to ‘Email’ and ‘Player Name’.

Now, we will update the ‘get_players’ query to use the text input components to search players by email or player name.

SELECT playaer_id, last_login, player_email, player_name, access_type 
FROM players 
WHERE
  CASE
    WHEN {{textInput3.value}} <> '' THEN player_email = {{textInput3.value}}
    WHEN {{textInput4.value}} <> '' THEN player_name = {{textInput4.value}}
    ELSE TRUE
  END
ORDER BY last_login DESC

Note that the default referenced names ‘textInput3’ and ‘textInput4’ may be different. If so, just change them to what they appear as in your app.

To test the new functionality, input a known player name or player email from the database and click ‘search’ to display a single player in the table.

Note the way our SQL query works. If both search fields are filled, the ‘CASE’ statement will use the email as it is first. If required  this can be changed by switching the order of the ‘WHEN’ lines.

Filter by Date Range

Now we will add a date filter to get players whose last login is between two dates. First, add a ‘Date range’ component to the search bar container and set the label text to ‘Last Login’.

Then, update the ‘get_players’ query to the following:

SELECT player_id, last_login, player_email, player_name, access_type 
FROM players 
WHERE
  CASE
    WHEN {{textInput3.value}} <> '' THEN player_email = {{textInput3.value}}
    WHEN {{textInput4.value}} <> '' THEN player_name = {{textInput4.value}}
    WHEN {{dateRange1.value.start}} <> '' AND {{dateRange1.value.end}} <> '' 
      THEN last_login BETWEEN {{dateRange1.value.start == '' ? new Date(0) : dateRange1.value.start}} 
                                           AND {{dateRange1.value.end == '' ? new Date() : dateRange1.value.end}}
    ELSE TRUE
  END
ORDER BY last_login DESC

You can test that searching still works with no parameters, then filter the search for any two dates between Sept 1st and Nov 30th 2023 which should return a filtered dataset.

Now we have implemented basic listing, searching and filtering functionalities in our app as well as updating and deleting data. The next section will improve the app UX for the end user and make it easier to extend and refactor for your developers.

Summary

Now we can search for player records via username or email and filter by their last login date.

From here, you can format components and implement a naming convention to make the app more usable for both users and developers.

Jump to section