`SortType` - Sorting Operations for SQL Queries

Overview of Sorting

Sorting rows in SQL queries is essential to order data in a desired sequence. The sort operation defines how the rows should be ordered. It can be applied either globally to an entire query or on specific tables and fields. The sort operation uses the format { field: 1 | -1 }, where:

1 represents ascending order.
-1 represents descending order.

In complex queries, sorting can be applied at multiple levels, such as sorting by different fields within various tables.

Sorting Examples

Example 1: Simple Sorting

Sort by a single field in ascending order (default sort).

const sort: SortType<['users']> = { name: 1 };
console.log(sort);
// Output: { name: 1 }

This will sort the rows by the name field in ascending order (ORDER BY name ASC).

Example 2: Descending Sorting

Sort by a field in descending order.

const sort: SortType<['users']> = { name: -1 };
console.log(sort);
// Output: { name: -1 }

This will sort the rows by the name field in descending order (ORDER BY name DESC).

Example 3: Sorting on Multiple Fields

You can sort on multiple fields in ascending or descending order.

const sort: SortType<['users']> = { age: -1, name: 1 };
console.log(sort);
// Output: { age: -1, name: 1 }

In this example, the query first sorts by age in descending order, and for users with the same age, it will sort by name in ascending order (ORDER BY age DESC, name ASC).

Example 4: Sorting Within Nested Tables

You can also apply sorting within nested tables, such as sorting the age field of the users table in descending order.

const sort: SortType<['users']> = { users: { age: -1 } };
console.log(sort);
// Output: { users: { age: -1 } }

Here, the rows are sorted by the age field of the users table in descending order (ORDER BY users.age DESC).

Example 5: Sorting with a Specific Table

For cases where you want to specify sorting on a specific table's column, you can define the table name and the column you want to sort.

const sort: SortType<['orders']> = { order_date: -1 };
console.log(sort);
// Output: { order_date: -1 }

This will sort the rows by the order_date field in descending order (ORDER BY order_date DESC).

Example 6: Combining Sorting with Joins

If you have multiple tables involved, you can apply sorting across different tables. Here's an example where we sort users by age in descending order and orders by order_date in ascending order:

const sort: SortType<['users', 'orders']> = { 
  users: { age: -1 },
  orders: { order_date: 1 }
};
console.log(sort);
// Output: { users: { age: -1 }, orders: { order_date: 1 } }

This will sort users by age in descending order, and for the related orders table, it will sort by order_date in ascending order.

Example 7: Global Sorting with String Fields

You can also sort by string fields globally. Here’s how you can sort by a string column, such as category, in ascending order.

const sort: SortType<['products']> = { category: 1 };
console.log(sort);
// Output: { category: 1 }

This sorts rows by category in ascending order (ORDER BY category ASC).

Complex Example with Multiple Tables

Here’s an example of how you would sort data from multiple tables, combining both ascending and descending order operations:

const sort: SortType<['orders', 'users', 'payments']> = { 
  orders: { order_date: -1 },  // Sort by order_date in descending order for the orders table
  users: { age: 1 },           // Sort by age in ascending order for the users table
  payments: { payment_amount: -1 } // Sort by payment_amount in descending order for the payments table
};

console.log(sort);
// Output: { orders: { order_date: -1 }, users: { age: 1 }, payments: { payment_amount: -1 } }

In this complex sorting example:

The rows in the orders table are sorted by order_date in descending order.
The rows in the users table are sorted by age in ascending order.
The rows in the payments table are sorted by payment_amount in descending order.

Conclusion

The sort operation provides a flexible and powerful way to control the order of your query results. It allows sorting by multiple fields, in both ascending and descending order, across different tables. Whether you're working with simple fields or need to sort nested tables, SortType offers a clean and efficient way to define your sorting criteria in TypeScript.

Sorting Operations

SortType - Sorting Operations for SQL Queries