Everything at once

Have filtering, sorting and pagination at the same time.

Now, back in the ProductsController, we may go to the findAll() route handler and use this composite DTO to validate and hold all the query params at the same time.

findAll(@Query() productsQueryDto: ProductsQueryDto) {
  return this.productsService.findAll(productsQueryDto);
}

In the namesake method in the service, let's then adjust the type of the parameter and extract all these new fields from it.

const { page, name, price, categoryId, sort, order } = productsQueryDto;

Now, inside the findAndCount() call, we'll also use the filtering and sorting options. To filter, we use the where option. First of all, we want to find products whose name contains the received name filter (if it was sent at all). Also, the casing of the text should not matter. To achieve this, we use

• ILike - The text should be present in the specified form. Generally used when a text may be present anywhere. It's also case-insensitive.

• Wildcard - The symbol % means anything, including nothing. As it is at both the beginning and end of the received filter, it means it will find products whose name may begin with anything, followed by the filter, and then end with anything. In other words, the filter may be anywhere.

• undefined - We just need to explicit this in case of unsent filter due to the ILike operator.

where: {
  name: name ? ILike(`%${name}%`) : undefined,
},

Below the name, we also filter by price. Obviously, a functionality to filter products simply by a specific price is not interesting at all. This will be improved afterwards.

price,

Finally, we also filter by category, requiring the one with the passed id.

categories: { id: categoryId },

Now, the only remaining step is sorting. This is done in the order option. If you test it a bit, you'll notice that it accepts fields from the entity, and the value must be either ASC or DESC.

This may reveal a problem: how can we sort by the field whose name is the value of the sort field? Fortunately, the following syntax allows for exactly that. Let's also already use the order field too.

order: { [sort]: order },

And we're done! We've got filtering, sorting and pagination when finding products. Outstanding!

Commit - Applying filtering and sorting