Custom sort and filter field with no alias

[danielkv]

Is your feature request related to a problem? Please describe. I'm trying to sort and filter by a field that's generated in selection, it's not on DB. But the system tries to put an alias which is not the case.

Describe the solution you'd like It may be a good solution to define allowed sorting fields and filters and how those would work individually

Additional context This is a custom selection I need to make

query.addSelect([
    "COMPANY_IS_OPEN(`meta`.`value`) as isOpen",
    "COMPANY_NEXT_OPEN_DATE(`meta`.`value`, NOW()) as nextOpen",
    "COMPANY_NEXT_CLOSE_DATE(`meta`.`value`) as nextClose",
   "COMPANY_ALLOW_BUY_CLOSED_BY_ID(`Company`.`id`) as allowBuyClosed",
]);

// order by (open | allowBuyClosed)
query.addOrderBy('isOpen OR (allowBuyClosed IS NOT NULL AND allowBuyClosed <> "false")', 'DESC');

I can't filter or sort by field isOpen or nextOpen

[thehappycoder]

I'd like to know too. @danielkv have you found a good workaround? I am thinking to create a database view, connect it to a typeorm model and then filter.

[danielkv]

I'd like to know too. @danielkv have you found a good workaround? I am thinking to create a database view, connect it to a typeorm model and then filter.

Hi, I didn't! I managed to make it work making a custom service, but wasn't the best way to go.

You can check my code in pronto-entregue-nest repository. I'm in the app now, could not copy the url. It's in nestjs-query branch.

Some part of the code is in Portuguese

If you need any help, I can try to help you out, just let me know here!

[thehappycoder]

@danielkv Thanks! There is a lot of code! :) Which commit from https://github.com/danielkv/pronto-entregue-nest/compare/nestjs-query?expand=1 is it?

I managed to implement it using typeorm @ViewEntity, which I linked to my other typeorm entity as a relation and then could use in the DTO and even filter by its props. Only had to make sure my @ViewEntity has @PrimaryColumn to workaround a possible bug in nestjs-query

[danielkv]

There is a lot of code! :) Which commit from https://github.com/danielkv/pronto-entregue-nest/compare/nestjs-query?expand=1 is it?

Yes, there is. kkk Actually it's was a project I was converting to use nestjs-query, didn't even had a chance to finish. I was talking about this file (Custom Service): https://github.com/danielkv/pronto-entregue-nest/blob/nestjs-query/src/modules/company-association/company/services/company.service.ts

I managed to implement it using typeorm @ViewEntity, which I linked to my other typeorm entity as a relation and then could use in the DTO and even filter by its props. Only had to make sure my @ViewEntity has @PrimaryColumn to workaround a possible bug in nestjs-query

I think with View it will work just fine. Probably it's a more elegant solution. I can't quite remember why I didn't use View, but I do remember I tried to.