Filtering Collection Resources

What Is Filtering?

Note

• Filtering is the process of selecting and retrieving a subset of data based on certain criteria.
• It allows clients to request only the data that meets their needs rather than receiving a large dataset and then processing it on the client side.

• Common Use Cases: For example, if you have an API that returns a list of species, you might want to filter this list based on parameters such as habitat, family, conservation status, or region.

---

Why Filtering Is Important?

Filtering Benefits

• Helps avoid over-fetching data.

Network Efficiency:

• Reduces bandwidth usage by transferring only relevant data
• Faster response times due to smaller payloads
• Lower server-side processing overhead

User Experience:

• Users get exactly the data they need
• Faster page loads and interactions
• Reduced client-side processing requirements

Scalability:

• Prevents overwhelming clients with large datasets
• Reduces server memory usage
• Enables handling of growing data volumes

---

How to Implement Filtering in RESTful Web Services?

1. Define filter criteria:

• Resource Identification: Determine which resource the filtering applies to. For example, if you have a species resource, the endpoint might be /species.
• Filtering Parameters: Decide on the parameters that clients can use to filter the data. These parameters could include various fields of the resource such as IDs, dates, names, or any other attributes relevant to your application.

Common Filter Types:

• Exact Match: status=active, category=electronics
• Range Queries: price_min=10&price_max=100, date_after=2023-01-01
• Pattern Matching: name_contains=smartphone, email_ends_with=@company.com
• List Membership: category_in=electronics,books,clothing

---

2. Design the API Endpoint:

Modify your API endpoints to accept filter parameters. Typically, filtering parameters are passed as query parameters in the URI of the GET request.

Example URI for filtering:

GET /api/species?habitat=forest&family=Felidae&conservation_status=endangered

In this example:

• habitat filters species by their natural habitat.
• family filters species by their taxonomic family.
• conservation_status filters species by their conservation status.

---

3. Implement Filtering Logic on the Server Side:

• Extract Parameters: Parse the query parameters from the request.
• Validate Parameters: Ensure that the parameters are valid and sanitize them to prevent issues such as SQL injection or other security vulnerabilities.
• Apply Filtering: Use the parameters to filter the data. This usually involves:
• Querying the database with filtering conditions.
• Iterating over a collection of objects and applying filter logic programmatically.

For example, in SQL, you might use:

SELECT * FROM species WHERE habitat = 'forest' AND family = 'Felidae' AND conservation_status = 'endangered';

• Querying the database with filtering conditions.
• Or, iterating over a collection of objects and applying filter logic programmatically.

For example, in SQL, you might use:

SELECT * FROM species WHERE habitat = 'forest' AND family = 'Felidae' AND conservation_status = 'endangered';

• Return the filtered data: Respond with the filtered data in the appropriate format (e.g., JSON or XML). Ensure that the response contains only the data that matches the criteria specified.

---

---

Filter Value Types

When designing filter parameters, it’s important to understand the different data types that can be used:

Value Type	Description	Example Query Parameters
string	Text values for exact or partial matching	?country=Japan, ?body_type=Mirrorless
integer	Whole numbers	?year=2020, ?generation=5
decimal	Numbers with decimal places	?price=1999.99, ?aperture=1.4
boolean	True/false values	?has_ibis=true, ?weather_sealed=false
date	Date values (typically YYYY-MM-DD format)	?release_date=2023-01-15

Boolean Handling

When handling boolean parameters in PHP, remember that query string values are strings. You’ll need to convert them:

$has_ibis = filter_var($has_ibis, FILTER_VALIDATE_BOOLEAN);

if (isset($has_ibis)) {
    $sql .= " AND has_ibis = :has_ibis";
    $params[':has_ibis'] = $has_ibis ? 1 : 0;
}

---

Range Filters

Range filters allow clients to filter data within a specific range of values. A common convention is to use _min and _max suffixes on parameter names.

Single-Sided Range

Filter with only a lower or upper bound:

GET /api/cameras?price_min=1000

Returns cameras costing at least $1000.

GET /api/cameras?weight_max=500

Returns cameras weighing at most 500 grams.

Paired Range (Both Bounds)

Filter within a specific range by providing both bounds:

GET /api/cameras?price_min=1000&price_max=3000

Returns cameras costing between $1000 and $3000.

SQL Pattern for Range Filters

SELECT * FROM cameras
WHERE price >= :price_min
  AND price <= :price_max

---

Date Filters

Date filters use _after and _before suffixes to filter records based on date fields.

Examples

GET /api/cameras?release_date_after=2020-01-01

Returns cameras released after January 1, 2020.

GET /api/cameras?release_date_before=2023-12-31

Returns cameras released before December 31, 2023.

GET /api/cameras?release_date_after=2020-01-01&release_date_before=2023-12-31

Returns cameras released between 2020 and 2023.

SQL Patterns for Date Filters

After only:

SELECT * FROM cameras
WHERE release_date > :date_after

Before only:

SELECT * FROM cameras
WHERE release_date < :date_before

Between (both bounds):

SELECT * FROM cameras
WHERE release_date > :date_after
  AND release_date < :date_before

---

Aggregation Filters (Count-Based)

Some filters require filtering based on aggregated data — for example, finding manufacturers that produce a minimum number of camera models. These require GROUP BY and HAVING clauses in SQL.

Example Use Case

Find manufacturers who produce at least 10 camera models:

GET /api/manufacturers?camera_count_min=10

SQL Pattern for Aggregation Filters

SELECT m.*, COUNT(cb.body_id) AS camera_count
FROM manufacturers m
LEFT JOIN camera_bodies cb ON m.manufacturer_id = cb.manufacturer_id
GROUP BY m.manufacturer_id
HAVING COUNT(cb.body_id) >= :min_count

Important

• Use HAVING (not WHERE) for aggregate conditions
• GROUP BY must include the primary key of the main table
• Use LEFT JOIN if you want to include entities with zero counts

---

Combining Multiple Filters

Multiple filters can be combined in a single request using & to separate parameters:

GET /api/cameras?price_min=1500&body_type=Mirrorless&has_ibis=true

This returns:

• Mirrorless cameras
• With image stabilization (IBIS)
• Costing at least $1500

Filter Summary

Suffix	Purpose	SQL Operator
_min	Minimum value (lower bound)	>=
_max	Maximum value (upper bound)	<=
_after	After a date	>
_before	Before a date	<
(none)	Exact match	=

---

Case-Insensitive Filtering

When filtering by string values, users might search using different cases:

• ?manufacturer=canon
• ?manufacturer=Canon
• ?manufacturer=CANON

All three should return the same results. Here’s how to implement case-insensitive filtering.

Using LOWER() Function

The most portable approach is to convert both the column and search term to lowercase:

SELECT * FROM manufacturers
WHERE LOWER(name) = LOWER(:search_term)

Performance Warning

Using LOWER() on a column prevents MySQL from using indexes on that column, which can slow down queries on large tables.

Using COLLATE (MySQL)

A more performant MySQL-specific approach is to use a case-insensitive collation:

SELECT * FROM manufacturers
WHERE name COLLATE utf8mb4_general_ci = :search_term

The utf8mb4_general_ci collation performs case-insensitive (ci) comparisons.

Using LIKE for Partial Matching

For partial, case-insensitive matching:

SELECT * FROM manufacturers
WHERE LOWER(name) LIKE LOWER(:search_pattern)

Best Practice

For production applications with large datasets, consider:

1. Storing a lowercase version of searchable columns
2. Using database-level collation settings
3. Using full-text search for complex search requirements