search

ppetzold / nestjs-paginate

Pagination and filtering helper method for TypeORM repositories or query builders using Nest.js framework :book::paperclip:
MIT License
428 stars 92 forks source link

Sort by non selected fields #969

Open wisac opened 2 weeks ago

wisac commented 2 weeks ago

In order to sort by a field, the field must be selected. This means sorting depends on selection.

Example ?sortBy=createdAt:DESC&select=name will throws a column distinctAlias.__root_name does not exist error even though createdAt is included in sortableColumns.

Ideally, I want to be able to sort by a field but not to return that field. Eg. sort by createdAt but not return createdAt.

Currently, I'm using a dto to get rid of the field I used for sorting and don't want to select it but I think it would be great to achieve this out of the box. Is there a way, am I missing something. I'm using postgres

Helveg commented 2 weeks ago

Hey! Thanks for your report. You are correct, the select mechanisms works rather "aggressively" on the database level: select=col1 will only SELECT (col1) FROM! This means that columns not included in the select simply do not exist to perform any querying on, and this is where your error comes from.

We ought to add any column we need to operate on to the columns we select.

This would solve your error, but then the not-selected-yet-queried column will appear in the response. With some additional logic we could either create a subquery, and then select only the truly selected columns from our subquery; or we could strip the unselected fields from the database response on the JS level.

Not sure what the best solution would look like.

Is this something you could draft a PR for? Perhaps the best way to begin tackling this is if you could open a PR with a test case that fails on this error? :)