Sort using a dynamic variable

Hey there! In this guide, we'll explore how to sort user reservations in a two-sided rental marketplace application built with Xano. Imagine you're logged into an Airbnb-like platform, and you want to view your upcoming reservations in chronological order, with the nearest one appearing first. Here's how you can achieve that using Xano's powerful features.

Setting the Stage

First, let's set up the data model for our application. We'll have three tables:

1. Users: Stores user information.
2. Listings: Contains details about rental properties.
3. Reservations: Tracks reservations made by users for specific listings, including check-in and check-out dates.

We'll create an endpoint that retrieves all reservations for the authenticated user and displays them in the desired order.

Fetching User Reservations

Let's start by querying the Reservations table to fetch all records where the user_id matches the authenticated user's ID:

SELECT * FROM Reservations WHERE user_id = @auth.id

This query will return all reservations made by the current user. However, the results won't be sorted in any particular order yet.

Formatting Timestamps

To make the reservation dates more readable, we'll use a loop to format the Unix timestamps into a human-friendly format:

{% for reservation in results.reservations %}
 {{ reservation.start_date | formatDate('YYYY-MM-DD') }} - {{ reservation.end_date | formatDate('YYYY-MM-DD') }}
{% endfor %}

This loop iterates over each reservation and displays the start and end dates using the formatDate filter provided by Xano.

Sorting Reservations

Now comes the interesting part: sorting the reservations based on the upcoming check-in date, with the nearest one appearing first.

Creating a Dynamic Variable

We'll create a dynamic variable called past to determine whether a reservation's end date is in the past or the future. This variable will be used for sorting purposes.

1. Go to the Output tab in your Creol record.
2. Click Add Evals and create a new evaluation field called past.
3. Add a filter using the Between comparison, with the left value set to 0 (representing the Unix epoch) and the right value set to now() (representing the current timestamp).

This filter will evaluate each reservation's end date against the current time and set the past field to true if the end date is in the past, or false if it's in the future.

Sorting the Results

With the past dynamic variable in place, we can now sort the results accordingly:

1. In the Output tab, click the Sort button.
2. Add a new sort rule for the past field in ascending order. This will place reservations with past=false (future reservations) first.
3. Add another sort rule for the start_date field, also in ascending order. This will ensure that future reservations are sorted chronologically based on their check-in dates.

After saving these changes, running the endpoint should now display the user's reservations in the desired order, with the upcoming reservation appearing first, followed by future reservations sorted by check-in date, and finally, any past reservations.

Conclusion

By leveraging Xano's powerful features like dynamic variables, filters, and sorting rules, you can create complex data manipulations and sorting logic without writing a single line of code. This example demonstrates how to sort user reservations in a two-sided rental marketplace, but the principles can be applied to various scenarios where you need to sort and present data in a specific order based on custom logic.

Remember, Xano empowers you to build powerful backend services visually, making it accessible to both non-technical users and developers alike. So, keep exploring and unleashing your creativity with Xano!