Implement sort by Affected Projects in Vulnerabilities list

[valentijnscholten]

Current Behavior

Currently the list of all vulnerabilities cannot be sorted by affected project count.

Proposed Behavior

Allow sorting the list of vulnerabilities by affected project count, as requested in https://github.com/DependencyTrack/frontend/issues/122

There can be several reasons to work on the most occurring vulnerabilities:

• triage them to make the state of the portfolio more accurate / up-to-date
• upgrade the affected component across the whole portfolio to reduce the risk of the portfolio
• ...

For us it's not uncommon to work on a specific vulnerability and have someone fix / upgrade the component in all the projects in the portfolio. Being able to sort on affected projects count will help to work on the most common vulnerabilities.

Checklist

• [X] I have read and understand the contributing guidelines
• [X] I have checked the existing issues for whether this enhancement was already requested

[setchy]

I had a brief look into this today.

The affectedProjectCount is a non-persistent field, therefore not sortable/orderable via JDO natively.

[valentijnscholten]

It doesn't have to be a persistent field, sorting can be done on aggregated values. Currently it's not possible however because the datamodel is not fully normalized and relationschips are missing in places, mainly around aliases. Maybe with a huge custom SQL query which might contain database specific pieces to make it work.

[setchy]

Thanks @valentijnscholten for the info.