Designing a Clean Separation for API Sorting Parameters and Database Columns

I'm working with a data mapper called UserMapper and a User model. Currently, UserMapper has a method called find_many. Now, a client makes requests like /api/users?sort_by=+last_name,-birthdate, so I'd like to allow sorting by multiple columns, including sort directions. The challenge I'm facing is deciding where to interpret and map these sort query parameters to the database column names. One option is to pass the sort fields directly (e.g., last_name, birthdate) to find_many and let it handle mapping them to the actual database columns. However, this approach forces the data mapper to be aware of the API's query parameter naming, which doesn't feel ideal. Alternatively, I could let the view/controller layer translate the API parameters to the respective database columns before invoking the data mapper. But again, this would make the presentation layer dependent on the database schema. What would be a reasonable design pattern or approach to tackle this separation of concerns while still supporting multiple sort fields with directions?

Jun 3, 2025 - 21:20

I'm working with a data mapper called UserMapper and a User model. Currently, UserMapper has a method called find_many. Now, a client makes requests like /api/users?sort_by=+last_name,-birthdate, so I'd like to allow sorting by multiple columns, including sort directions. The challenge I'm facing is deciding where to interpret and map these sort query parameters to the database column names.

One option is to pass the sort fields directly (e.g., last_name, birthdate) to find_many and let it handle mapping them to the actual database columns. However, this approach forces the data mapper to be aware of the API's query parameter naming, which doesn't feel ideal.

Alternatively, I could let the view/controller layer translate the API parameters to the respective database columns before invoking the data mapper. But again, this would make the presentation layer dependent on the database schema.

What would be a reasonable design pattern or approach to tackle this separation of concerns while still supporting multiple sort fields with directions?