Dynamic Order By & Custom Pagination

Question

I couldn't find a similar post, so I'll give it a try. I'm currently working on a Fullstack Symfony 6 Project (utilizing React and Symfony 6), mostly focusing on API development. However, I've hit a roadblock. I've implemented custom Pagination with Doctrine (avoiding the use of other bundles). The issue arises when attempting to Order By columns by clicking in the Frontend; it only affects the current page's data. The goal is to Order it by all entries. What would be the best practice in this scenario? Should I call another API to retrieve all entries? Could you provide an example?

Repository (Query Builder)

class CustomerRepository extends ServiceEntityRepository
{
    public function __construct(ManagerRegistry $registry)
    {
        parent::__construct($registry, Customer::class);
    }

    public function getCustomers(
        string $column,
        string $type,
        int    $page = 1,
        int    $entriesPerPage = 10): array
    {
        $query = $this->createQueryBuilder('d')
            ->orderBy("d.$column", $type)
            ->getQuery();
        $query->setFirstResult($entriesPerPage * ($page - 1))
            ->setMaxResults($entriesPerPage);

        $paginator = new Paginator($query);
        $total = $paginator->count();
        $lastPage = (int)ceil($total / $entriesPerPage);

        return [
            'customers' => $paginator,
            'lastPage' => $lastPage,
            'total' => $total
        ];

    }
}

Controller (API)

    #[Route(path: "/api/customers/{column}/{type}/{page}", name: "customer_get", methods: ["GET"])]
    public function getCustomers(
        CustomerService $customerService,
        string $column,
        string $type,
        int $page=1
    ): JsonResponse
    {
        return $this->json($customerService->getCustomers($column, $type, $page));
    }

Response (called route: api/customers/created_at/asc/3)

{
  "customers": [
    {
      "id": 156,
      "name": "Test",
      "created_at": "2024/03/07",
      "updated_at": null
    },
    {
      "id": 157,
      "name": "Test",
      "created_at": "2024/03/07",
      "updated_at": null
    }
  ],
  "lastPage": 3,
  "total": 11
}

React

    const [sortBy, setSortBy] = useState({field: "name", order: "ASC"});
    const [sortByCreatedAt, setSortByCreatedAt] = useState(null);

    const fetchCustomers = () => {
        const sortField = sortByCreatedAt ? 'created_at' : sortBy.field;
        const sortOrder = sortByCreatedAt ? sortByCreatedAt.order : sortBy.order;


        axios.get(`/api/customers/${sortField}/${sortOrder}/${currentPage}`)
            .then(response => {
                setCustomers(response.data.departments);
                setLastPage(response.data.lastPage)
            })
            .catch(error => {
                console.error(error);
            });
    };

Question 2: Are there any possibilities to improve my "Order By" clause in the Query Builder? Can it be made more dynamic, or is it fine as it is?

Answer 1

The issue arises when attempting to Order By columns by clicking in the Frontend; it only affects the current page's data. The goal is to Order it by all entries.

Yes, and that's logical because each time you're sending the same sortField and sortOrder values to the API, you're not updating these values inside the sortBy object when a user changes the order of sort or the column used in sorting.

Instead, you will need to update these values whenever a user changes the ordering behavior, for example, if I want to sort by email column in descending order, the sortBy object should look like that:

{
    field: "email",
    order: "DESC"
}

I'm not a React developer, and I'm not sure about the best practices regarding this situation, but logically you need to call the setSortBy() function inside the function where you're doing the client side sorting, and then you need to call the fetchCustomers() function after that, for example:

setSortBy(...);
fetchCustomers();

And disable any client-side sorting. So I suggest letting the entire sorting process be handled by the server and not doing anything on the client.

Are there any possibilities to improve my "Order By" clause in the Query Builder? Can it be made more dynamic, or is it fine as it is?

You could allow multiple sorting criteria with different directions, for example, you could allow sorting by name column in descending order and sorting by created_at column in ascending order. This way, if multiple rows have the same value inside the name column, they will get sorted by the created_at in ascending order.

Inside your CustomerRepository class, you may need to do something like this:

class CustomerRepository extends ServiceEntityRepository
{
    public function getCustomers(
        array $columns,           // <--- becomes an array of columns
        array $types,             // <--- becomes an array of sorting directions
        int    $page = 1,
        int    $entriesPerPage = 10): array
    {
        $builder = $this->createQueryBuilder('d');

        // add sorting criteria to the code
        for ($i = 0; $i < count($columns); $i++) {
            $column = $columns[$i];
            $type = $types[$i];
            $builder->orderBy("d.$column", $type);
        }

        $query = $builder->getQuery();
        $query->setFirstResult($entriesPerPage * ($page - 1))
            ->setMaxResults($entriesPerPage);

        $paginator = new Paginator($query);
        $total = $paginator->count();
        $lastPage = (int)ceil($total / $entriesPerPage);

        return [
            'customers' => $paginator,
            'lastPage' => $lastPage,
            'total' => $total
        ];

    }
}

However, this piece of code is dangerous:

$query = $this->createQueryBuilder('d')
    ->orderBy("d.$column", $type)
    ->getQuery();

You may be vulnerable to SQL injection attacks, because you're passing the column name as a variable, and these parts of an SQL query can't use query binds. For more information please consult the SQL Injection Prevention page on OWASP cheatsheet website.