Sorting Collection Resources

What Is Sorting?

Sorting arranges data in a specific order (ascending or descending) based on one or more fields. In REST APIs, sorting allows clients to retrieve data in their preferred order using query parameters.

Common sorting scenarios:

• Cameras sorted by price (lowest to highest).
• Lenses sorted by focal length (shortest to longest).
• Manufacturers sorted alphabetically by name.

---

Why Implement Sorting?

• Users can organize data the way they want, making your API more useful.
• It helps users find what they’re looking for faster (newest posts, cheapest products, etc.).
• Sorting at the database level is much more efficient than making clients sort the data themselves.
• When combined with pagination, sorting helps you manage large datasets without overwhelming the response.

---

Key Concepts of Sorting

1. Order: Sorting can be done in various orders:

   • Ascending: From the lowest to the highest value (e.g., A-Z, 0-9).
   • Descending: From the highest to the lowest value (e.g., Z-A, 9-0).

2. Fields: The data can be sorted based on specific fields or attributes, such as:

   • Strings: (e.g., names).
   • Numbers: (e.g., prices).
   • Dates: (e.g., timestamps).

3. Multiple sorts: You can sort by multiple fields, where the first field is the primary sort, and the subsequent fields are used as tiebreakers.

   For example, to sort items by price ascending and then by name descending:

   GET /items?sort_by=price,name&order=asc,desc

---

How Sorting is Implemented in RESTful Web Services

A. Defining Sorting Parameters

In RESTful APIs, sorting is usually implemented through query parameters. Clients can specify sorting preferences in their request. Common query parameters for sorting might include:

• sort_by: Indicates the field by which to sort.
• order: Indicates the sort order, such as ascending (asc) or descending (desc).

Example URL with sorting parameters:

GET /items?sort_by=name&order=asc

In this example:

• sort_by=name indicates that the results should be sorted by the name field.
• order=asc specifies that the sorting should be in ascending order.

Default Sorting

Always define a default sort field and order for when the client does not provide sorting parameters. A common default is sorting by id in ascending order.

This is especially important when sorting is combined with pagination. Without a consistent default sort, the same query can return different results across pages, leading to duplicated or missing records.

---

B. Handling Sorting Parameters on the Server-Side

On the server side, you need to handle these query parameters to adjust the data retrieval logic. Here’s how you typically handle this in a PHP application. The following example illustrates retrieving sorting parameters from the request object in Slim framework.

1. Retrieve Query Parameters

   Fetch the sorting parameters from the request URL.

   $params = $request->getQueryParams();
   $sortBy = isset($params['sort_by']) ? $params['sort_by'] : 'id';
   $order = isset($params['order']) ? $params['order'] : 'asc';

2. Validate and Sanitize Parameters

   Always validate sorting parameters to prevent SQL injection and ensure data integrity.

   // Define allowed fields and validate input
   $validSortByFields = ['id', 'name', 'price', 'created_at'];
   $validOrders = ['asc', 'desc'];
   
   // Validate sort field
   if (!in_array($sortBy, $validSortByFields)) {
       throw new InvalidArgumentException("Invalid sort field: {$sortBy}");
   }
   
   // Validate sort order
   if (!in_array(strtolower($order), $validOrders)) {
       throw new InvalidArgumentException("Invalid sort order: {$order}");
   }
   
   $order = strtoupper($order); // Ensure consistent case

3. Build the SQL Query Safely

   ORDER BY column names cannot be bound via PDO placeholders, so we rely on the whitelist validation above to ensure only safe values are used.

   // Safe because $sortBy and $order are validated against a whitelist
   $sql = "SELECT * FROM items ORDER BY {$sortBy} {$order}";
   
   // For additional safety, you can map fields to actual column names
   $fieldMapping = [
       'id' => 'item_id',
       'name' => 'item_name',
       'price' => 'item_price',
       'created_at' => 'created_at'
   ];
   
   $actualField = $fieldMapping[$sortBy] ?? 'item_id';
   $sql = "SELECT * FROM items ORDER BY {$actualField} {$order}";

4. Execute the Query

   Once the SQL query is built, execute it using PDO as you normally would.

Sort Stability

When sorting by a non-unique field (e.g., price), the order of ties is undefined and can vary between queries. Always append a unique secondary sort column (e.g., id) to guarantee deterministic ordering:

SELECT * FROM items ORDER BY price ASC, id ASC

This is critical when combining sorting with pagination. Without a stable sort, records can appear on multiple pages or be skipped entirely.

---

Things to Consider

Security

• Never allow arbitrary field names. Whitelist the fields that can be sorted to prevent SQL injection.
• Validate both field names and sort directions strictly.
• Remember that ORDER BY columns cannot be bound via PDO placeholders, so whitelist validation is your primary defense.

Performance

• Sorting on non-indexed columns can be slow on large datasets. Ensure that columns used for sorting have appropriate database indexes.
• When combining sorting with pagination, the database must sort the entire result set before applying LIMIT/OFFSET, so efficient indexes are especially important.

API Design

• Document which fields can be sorted and what format to use.
• Stick to a consistent parameter naming convention (sort_by, order, etc.).
• Return meaningful error messages when someone uses an invalid sort parameter.