Is it possible to implement sorting by different fields in a table without code duplication?

Question

symfony

Good afternoon, you need to implement on the backend sorting of records from the table by various fields that are taken using Doctrine QueryBuilder and issue to the rest api endpoint, is it possible to somehow parameterize the query to the database so that without duplicating the query for a selection in a dozen methods, get records sorted by one of the fields?

Answer 1

If you operate with the text of the request, then add a parameter to order by.

order by case 
            when :order_param = 1 then col_1
            when :order_param = 2 then col_2
           end,
           case 
            when :order_param = 1 then col_2
            when :order_param = 2 then col_1
           end

This means that if :order_param = 1 is given, then sorting will be equivalent to order by col_1, col_2.
If :order_param = 2 then order by col_2, col_1.
The case can also be embedded in subqueries if you have a complex query structure.