# Bug SPPM-78 - Sorting by custom field has strong impact on performance for the project list

Project: Stream Project Portfolio Management
Status: closed
URL: https://community.openproject.org/work_packages/SPPM-78

## Open points

_No work packages_

## Details

| Attribute | Value |
| --- | --- |
| Date |  |
| Priority | Medium |
| Module |  |
| Target versions | 15.1.0 |
| Observed in versions |  |
| Bug found in version (deprecated) |  |
| Requires doc change | No |
| Accessibility | No |
| Pull preview |  |
| List |  |
| Labels |  |

## People

| Attribute | Value |
| --- | --- |
| Assignee |  |
| Accountable |  |
| Product manager |  |
| Developers |  |
| Designer |  |
| Ordered by |  |
| Signed off by |  |
| Interest by |  |

## Planning

| Attribute | Value |
| --- | --- |
| Relevance |  |

## Costs

| Attribute | Value |
| --- | --- |
| Votes |  |
| Work |  |
| Labor costs |  |

## Description

### Steps to reproduce

Reproduction steps:

1.  Have at least 200 projects
    
2.  Have a large number of users (community has ~90K, generated to 100K)
    
3.  Have a large number of versions (generated 100K)
    
4.  Have project attributes of type user and type version
    
5.  Go to the project index page, add the custom fields as column and sort by one of them
    

OR

1.  Have a larger body of work packages
    
2.  Have a larger number of custom values
    
3.  Have a larger number of users
    
4.  Have custom fields (most effect have those of type user)
    
5.  Go to the work package index page and sort by one or many of the custom fields
    

### What is the buggy behavior?

*   Very poor performance
    

### What is the expected behavior?

1.  Ok performance
    

### Additional notes

*   Appsignal: [https://appsignal.com/openproject-gmbh/sites/66b224a4d30d867bed8a1772/performance/incidents/46/samples/66b224a4d30d867bed8a1772-53228314355367163951723811820](https://appsignal.com/openproject-gmbh/sites/66b224a4d30d867bed8a1772/performance/incidents/46/samples/66b224a4d30d867bed8a1772-53228314355367163951723811820)
    
*   As the problem occurred when robots tried to index the page, the links on the table headers should receive "nofollow" attributes (Done in [https://github.com/opf/openproject/pull/16458)](https://github.com/opf/openproject/pull/16458))
    

The problem seems to stem from the order being carried out by individual subselects. In the case of user custom fields, 3 subselects are used for sorting by each custom field. And to satisfy postgresql, the same 3 subselects are used in the selects again. So when sorting by 3 custom fields, 18 subselects are in place. Those seem to be executed individually by posgresql leading to the poor performance. The SQL looks similar to this:

```SQL
SELECT     (SELECT lastname user_cv_lastname FROM users cv_user
     WHERE cv_user.id =     (SELECT CAST(cv_sort.value AS decimal(60,3)) FROM custom_values cv_sort
      WHERE       cv_sort.customized_type='Project'
      AND cv_sort.customized_id=projects.id
      AND cv_sort.custom_field_id=353

      AND cv_sort.value <> ''
      AND cv_sort.value IS NOT NULL
    LIMIT 1)

     LIMIT 1)
,     (SELECT firstname user_cv_firstname FROM users cv_user
     WHERE cv_user.id =     (SELECT CAST(cv_sort.value AS decimal(60,3)) FROM custom_values cv_sort
      WHERE       cv_sort.customized_type='Project'
      AND cv_sort.customized_id=projects.id
      AND cv_sort.custom_field_id=353

      AND cv_sort.value <> ''
      AND cv_sort.value IS NOT NULL
    LIMIT 1)

     LIMIT 1)
,     (SELECT id user_cv_id FROM users cv_user
     WHERE cv_user.id =     (SELECT CAST(cv_sort.value AS decimal(60,3)) FROM custom_values cv_sort
      WHERE       cv_sort.customized_type='Project'
      AND cv_sort.customized_id=projects.id
      AND cv_sort.custom_field_id=353

      AND cv_sort.value <> ''
      AND cv_sort.value IS NOT NULL
    LIMIT 1)

     LIMIT 1)
,     (SELECT lastname user_cv_lastname FROM users cv_user
     WHERE cv_user.id =     (SELECT CAST(cv_sort.value AS decimal(60,3)) FROM custom_values cv_sort
      WHERE       cv_sort.customized_type='Project'
      AND cv_sort.customized_id=projects.id
      AND cv_sort.custom_field_id=389

      AND cv_sort.value <> ''
      AND cv_sort.value IS NOT NULL
    LIMIT 1)

     LIMIT 1)
,     (SELECT firstname user_cv_firstname FROM users cv_user
     WHERE cv_user.id =     (SELECT CAST(cv_sort.value AS decimal(60,3)) FROM custom_values cv_sort
      WHERE       cv_sort.customized_type='Project'
      AND cv_sort.customized_id=projects.id
      AND cv_sort.custom_field_id=389

      AND cv_sort.value <> ''
      AND cv_sort.value IS NOT NULL
    LIMIT 1)

     LIMIT 1)
,     (SELECT id user_cv_id FROM users cv_user
     WHERE cv_user.id =     (SELECT CAST(cv_sort.value AS decimal(60,3)) FROM custom_values cv_sort
      WHERE       cv_sort.customized_type='Project'
      AND cv_sort.customized_id=projects.id
      AND cv_sort.custom_field_id=389

      AND cv_sort.value <> ''
      AND cv_sort.value IS NOT NULL
    LIMIT 1)

     LIMIT 1)
,     (SELECT lastname user_cv_lastname FROM users cv_user
     WHERE cv_user.id =     (SELECT CAST(cv_sort.value AS decimal(60,3)) FROM custom_values cv_sort
      WHERE       cv_sort.customized_type='Project'
      AND cv_sort.customized_id=projects.id
      AND cv_sort.custom_field_id=388

      AND cv_sort.value <> ''
      AND cv_sort.value IS NOT NULL
    LIMIT 1)

     LIMIT 1)
,     (SELECT firstname user_cv_firstname FROM users cv_user
     WHERE cv_user.id =     (SELECT CAST(cv_sort.value AS decimal(60,3)) FROM custom_values cv_sort
      WHERE       cv_sort.customized_type='Project'
      AND cv_sort.customized_id=projects.id
      AND cv_sort.custom_field_id=388

      AND cv_sort.value <> ''
      AND cv_sort.value IS NOT NULL
    LIMIT 1)

     LIMIT 1)
,     (SELECT id user_cv_id FROM users cv_user
     WHERE cv_user.id =     (SELECT CAST(cv_sort.value AS decimal(60,3)) FROM custom_values cv_sort
      WHERE       cv_sort.customized_type='Project'
      AND cv_sort.customized_id=projects.id
      AND cv_sort.custom_field_id=388

      AND cv_sort.value <> ''
      AND cv_sort.value IS NOT NULL
    LIMIT 1)

     LIMIT 1)
, projects.*, wiki.filesize AS wiki_required_space, wp.filesize AS work_package_required_space, repos.required_storage_bytes AS repositories_required_space, (COALESCE(wiki.filesize, 0) + COALESCE(wp.filesize, 0) + COALESCE(repos.required_storage_bytes, 0)) AS required_disk_space, "activity"."latest_activity_at" FROM projects projects LEFT JOIN (SELECT wiki.project_id, SUM(wiki_attached.filesize) AS filesize FROM wikis wiki JOIN wiki_pages pages ON pages.wiki_id = wiki.id JOIN attachments wiki_attached ON (wiki_attached.container_id = pages.id AND wiki_attached.container_type = 'WikiPage') GROUP BY wiki.project_id) wiki ON projects.id = wiki.project_id LEFT JOIN (SELECT wp.project_id, SUM(wp_attached.filesize) AS filesize FROM work_packages wp JOIN attachments wp_attached ON (wp_attached.container_id = wp.id AND wp_attached.container_type = 'WorkPackage') GROUP BY wp.project_id) wp ON projects.id = wp.project_id LEFT JOIN repositories repos ON repos.project_id = projects.id LEFT JOIN (        SELECT project_id, MAX(updated_at) latest_activity_at
        FROM (        SELECT project_id project_id, MAX(work_packages.updated_at) updated_at
        FROM work_packages

        WHERE work_packages.updated_at IS NOT NULL
        GROUP BY project_id
 UNION ALL         SELECT id project_id, MAX(projects.updated_at) updated_at
        FROM projects

        WHERE projects.updated_at IS NOT NULL
        GROUP BY project_id
 UNION ALL         SELECT project_id project_id, MAX(changesets.committed_on) updated_at
        FROM repositories
                LEFT OUTER JOIN changesets
        ON repositories.id =
           changesets.repository_id

        WHERE changesets.committed_on IS NOT NULL
        GROUP BY project_id
 UNION ALL         SELECT project_id project_id, MAX(news.updated_at) updated_at
        FROM news

        WHERE news.updated_at IS NOT NULL
        GROUP BY project_id
 UNION ALL         SELECT project_id project_id, MAX(wiki_pages.updated_at) updated_at
        FROM wikis
                LEFT OUTER JOIN wiki_pages
        ON wikis.id =
           wiki_pages.wiki_id

        WHERE wiki_pages.updated_at IS NOT NULL
        GROUP BY project_id
 UNION ALL         SELECT project_id project_id, MAX(messages.updated_at) updated_at
        FROM forums
                LEFT OUTER JOIN messages
        ON forums.id =
           messages.forum_id

        WHERE messages.updated_at IS NOT NULL
        GROUP BY project_id
 UNION ALL         SELECT project_id project_id, MAX(time_entries.updated_at) updated_at
        FROM time_entries

        WHERE time_entries.updated_at IS NOT NULL
        GROUP BY project_id
 UNION ALL         SELECT project_id project_id, MAX(meetings.updated_at) updated_at
        FROM meetings

        WHERE meetings.updated_at IS NOT NULL
        GROUP BY project_id
 UNION ALL         SELECT project_id project_id, MAX(budgets.updated_at) updated_at
        FROM budgets

        WHERE budgets.updated_at IS NOT NULL
        GROUP BY project_id
) activity
        GROUP BY project_id
) activity ON projects.id = activity.project_id WHERE (projects.active IN ('t')) 
	
	ORDER BY     (SELECT lastname user_cv_lastname FROM users cv_user
     WHERE cv_user.id =     (SELECT CAST(cv_sort.value AS decimal(60,3)) FROM custom_values cv_sort
      WHERE       cv_sort.customized_type='Project'
      AND cv_sort.customized_id=projects.id
      AND cv_sort.custom_field_id=353

      AND cv_sort.value <> ''
      AND cv_sort.value IS NOT NULL
    LIMIT 1)

     LIMIT 1)
 asc,     (SELECT firstname user_cv_firstname FROM users cv_user
     WHERE cv_user.id =     (SELECT CAST(cv_sort.value AS decimal(60,3)) FROM custom_values cv_sort
      WHERE       cv_sort.customized_type='Project'
      AND cv_sort.customized_id=projects.id
      AND cv_sort.custom_field_id=353

      AND cv_sort.value <> ''
      AND cv_sort.value IS NOT NULL
    LIMIT 1)

     LIMIT 1)
 asc,     (SELECT id user_cv_id FROM users cv_user
     WHERE cv_user.id =     (SELECT CAST(cv_sort.value AS decimal(60,3)) FROM custom_values cv_sort
      WHERE       cv_sort.customized_type='Project'
      AND cv_sort.customized_id=projects.id
      AND cv_sort.custom_field_id=353

      AND cv_sort.value <> ''
      AND cv_sort.value IS NOT NULL
    LIMIT 1)

     LIMIT 1)
 asc,     (SELECT lastname user_cv_lastname FROM users cv_user
     WHERE cv_user.id =     (SELECT CAST(cv_sort.value AS decimal(60,3)) FROM custom_values cv_sort
      WHERE       cv_sort.customized_type='Project'
      AND cv_sort.customized_id=projects.id
      AND cv_sort.custom_field_id=389

      AND cv_sort.value <> ''
      AND cv_sort.value IS NOT NULL
    LIMIT 1)

     LIMIT 1)
 asc,     (SELECT firstname user_cv_firstname FROM users cv_user
     WHERE cv_user.id =     (SELECT CAST(cv_sort.value AS decimal(60,3)) FROM custom_values cv_sort
      WHERE       cv_sort.customized_type='Project'
      AND cv_sort.customized_id=projects.id
      AND cv_sort.custom_field_id=389

      AND cv_sort.value <> ''
      AND cv_sort.value IS NOT NULL
    LIMIT 1)

     LIMIT 1)
 asc,     (SELECT id user_cv_id FROM users cv_user
     WHERE cv_user.id =     (SELECT CAST(cv_sort.value AS decimal(60,3)) FROM custom_values cv_sort
      WHERE       cv_sort.customized_type='Project'
      AND cv_sort.customized_id=projects.id
      AND cv_sort.custom_field_id=389

      AND cv_sort.value <> ''
      AND cv_sort.value IS NOT NULL
    LIMIT 1)

     LIMIT 1)
 asc,     (SELECT lastname user_cv_lastname FROM users cv_user
     WHERE cv_user.id =     (SELECT CAST(cv_sort.value AS decimal(60,3)) FROM custom_values cv_sort
      WHERE       cv_sort.customized_type='Project'
      AND cv_sort.customized_id=projects.id
      AND cv_sort.custom_field_id=388

      AND cv_sort.value <> ''
      AND cv_sort.value IS NOT NULL
    LIMIT 1)

     LIMIT 1)
 asc,     (SELECT firstname user_cv_firstname FROM users cv_user
     WHERE cv_user.id =     (SELECT CAST(cv_sort.value AS decimal(60,3)) FROM custom_values cv_sort
      WHERE       cv_sort.customized_type='Project'
      AND cv_sort.customized_id=projects.id
      AND cv_sort.custom_field_id=388

      AND cv_sort.value <> ''
      AND cv_sort.value IS NOT NULL
    LIMIT 1)

     LIMIT 1)
 asc,     (SELECT id user_cv_id FROM users cv_user
     WHERE cv_user.id =     (SELECT CAST(cv_sort.value AS decimal(60,3)) FROM custom_values cv_sort
      WHERE       cv_sort.customized_type='Project'
      AND cv_sort.customized_id=projects.id
      AND cv_sort.custom_field_id=388

      AND cv_sort.value <> ''
      AND cv_sort.value IS NOT NULL
    LIMIT 1)

     LIMIT 1)
 asc, "projects"."id" DESC LIMIT 500 OFFSET 1
```

This can be reworked with CTEs to e.g.

```SQL
WITH cv_353 AS (
 (SELECT cv_user.*, custom_values.customized_id FROM users cv_user
     JOIN custom_values ON
	 CAST(custom_values.value AS decimal(60,3)) = cv_user.id
      AND custom_values.customized_type='Project'
      AND custom_values.custom_field_id=353)	
),
	cv_389 AS (
 (SELECT cv_user.*, custom_values.customized_id FROM users cv_user
     JOIN custom_values ON
	 CAST(custom_values.value AS decimal(60,3)) = cv_user.id
      AND custom_values.customized_type='Project'
      AND custom_values.custom_field_id=389)	
),
	cv_388 AS (
 (SELECT cv_user.*, custom_values.customized_id FROM users cv_user
     JOIN custom_values ON
	 CAST(custom_values.value AS decimal(60,3)) = cv_user.id
      AND custom_values.customized_type='Project'
      AND custom_values.custom_field_id=388)	
)
	
	


SELECT     (SELECT lastname user_cv_lastname FROM cv_353
     WHERE cv_353.customized_id=projects.id
    LIMIT 1)
,     (SELECT firstname user_cv_firstname FROM cv_353
     WHERE cv_353.customized_id=projects.id
    LIMIT 1)
,     (SELECT id user_cv_id FROM cv_353
     WHERE cv_353.customized_id=projects.id
    LIMIT 1)
,     (SELECT lastname user_cv_lastname FROM cv_389
     WHERE cv_389.customized_id=projects.id
    LIMIT 1)
,     (SELECT firstname user_cv_firstname FROM cv_389
     WHERE cv_389.customized_id=projects.id
    LIMIT 1)
,     (SELECT id user_cv_id FROM cv_389
     WHERE cv_389.customized_id=projects.id
    LIMIT 1)
,     (SELECT lastname user_cv_lastname FROM cv_388
     WHERE cv_388.customized_id=projects.id
    LIMIT 1)
,     (SELECT firstname user_cv_firstname FROM cv_388
     WHERE cv_388.customized_id=projects.id
    LIMIT 1)
,     (SELECT id user_cv_id FROM cv_388
     WHERE cv_388.customized_id=projects.id
    LIMIT 1)
, projects.*, wiki.filesize AS wiki_required_space, wp.filesize AS work_package_required_space, repos.required_storage_bytes AS repositories_required_space, (COALESCE(wiki.filesize, 0) + COALESCE(wp.filesize, 0) + COALESCE(repos.required_storage_bytes, 0)) AS required_disk_space, "activity"."latest_activity_at" FROM projects projects LEFT JOIN (SELECT wiki.project_id, SUM(wiki_attached.filesize) AS filesize FROM wikis wiki JOIN wiki_pages pages ON pages.wiki_id = wiki.id JOIN attachments wiki_attached ON (wiki_attached.container_id = pages.id AND wiki_attached.container_type = 'WikiPage') GROUP BY wiki.project_id) wiki ON projects.id = wiki.project_id LEFT JOIN (SELECT wp.project_id, SUM(wp_attached.filesize) AS filesize FROM work_packages wp JOIN attachments wp_attached ON (wp_attached.container_id = wp.id AND wp_attached.container_type = 'WorkPackage') GROUP BY wp.project_id) wp ON projects.id = wp.project_id LEFT JOIN repositories repos ON repos.project_id = projects.id LEFT JOIN (        SELECT project_id, MAX(updated_at) latest_activity_at
        FROM (        SELECT project_id project_id, MAX(work_packages.updated_at) updated_at
        FROM work_packages

        WHERE work_packages.updated_at IS NOT NULL
        GROUP BY project_id
 UNION ALL         SELECT id project_id, MAX(projects.updated_at) updated_at
        FROM projects

        WHERE projects.updated_at IS NOT NULL
        GROUP BY project_id
 UNION ALL         SELECT project_id project_id, MAX(changesets.committed_on) updated_at
        FROM repositories
                LEFT OUTER JOIN changesets
        ON repositories.id =
           changesets.repository_id

        WHERE changesets.committed_on IS NOT NULL
        GROUP BY project_id
 UNION ALL         SELECT project_id project_id, MAX(news.updated_at) updated_at
        FROM news

        WHERE news.updated_at IS NOT NULL
        GROUP BY project_id
 UNION ALL         SELECT project_id project_id, MAX(wiki_pages.updated_at) updated_at
        FROM wikis
                LEFT OUTER JOIN wiki_pages
        ON wikis.id =
           wiki_pages.wiki_id

        WHERE wiki_pages.updated_at IS NOT NULL
        GROUP BY project_id
 UNION ALL         SELECT project_id project_id, MAX(messages.updated_at) updated_at
        FROM forums
                LEFT OUTER JOIN messages
        ON forums.id =
           messages.forum_id

        WHERE messages.updated_at IS NOT NULL
        GROUP BY project_id
 UNION ALL         SELECT project_id project_id, MAX(time_entries.updated_at) updated_at
        FROM time_entries

        WHERE time_entries.updated_at IS NOT NULL
        GROUP BY project_id
 UNION ALL         SELECT project_id project_id, MAX(meetings.updated_at) updated_at
        FROM meetings

        WHERE meetings.updated_at IS NOT NULL
        GROUP BY project_id
 UNION ALL         SELECT project_id project_id, MAX(budgets.updated_at) updated_at
        FROM budgets

        WHERE budgets.updated_at IS NOT NULL
        GROUP BY project_id
) activity
        GROUP BY project_id
) activity ON projects.id = activity.project_id WHERE (projects.active IN ('t')) 
	
	ORDER BY     (SELECT lastname user_cv_lastname FROM cv_353
     WHERE cv_353.customized_id=projects.id
    LIMIT 1)
 asc,     (SELECT firstname user_cv_firstname FROM cv_353
     WHERE cv_353.customized_id=projects.id
    LIMIT 1)
 asc,     (SELECT id user_cv_id FROM cv_353
     WHERE cv_353.customized_id=projects.id
    LIMIT 1)
 asc,     (SELECT lastname user_cv_lastname FROM cv_389
     WHERE cv_389.customized_id=projects.id
    LIMIT 1)
 asc,     (SELECT firstname user_cv_firstname FROM cv_389
     WHERE cv_389.customized_id=projects.id
    LIMIT 1)
 asc,     (SELECT id user_cv_id FROM cv_389
     WHERE cv_389.customized_id=projects.id
    LIMIT 1)
 asc,     (SELECT lastname user_cv_lastname FROM cv_388
     WHERE cv_388.customized_id=projects.id
    LIMIT 1)
 asc,     (SELECT firstname user_cv_firstname FROM cv_388
     WHERE cv_388.customized_id=projects.id
    LIMIT 1)
 asc,     (SELECT id user_cv_id FROM cv_388
     WHERE cv_388.customized_id=projects.id
    LIMIT 1)
 asc, "projects"."id" DESC LIMIT 250 OFFSET 1
```

Or with joins like this:

```SQL
SELECT     cv_353.lastname 
,   cv_353.firstname
,	cv_353.id
,   cv_389.lastname 
,   cv_389.firstname
,	cv_389.id
,   cv_388.lastname 
,   cv_388.firstname
,	cv_388.id
, projects.*, wiki.filesize AS wiki_required_space, wp.filesize AS work_package_required_space, repos.required_storage_bytes AS repositories_required_space, (COALESCE(wiki.filesize, 0) + COALESCE(wp.filesize, 0) + COALESCE(repos.required_storage_bytes, 0)) AS required_disk_space, "activity"."latest_activity_at" 
	FROM projects projects
	LEFT JOIN (
	(SELECT cv_user.*, custom_values.customized_id FROM users cv_user
     JOIN custom_values ON
	 CAST(custom_values.value AS decimal(60,3)) = cv_user.id
      AND custom_values.customized_type='Project'
      AND custom_values.custom_field_id=389)
	) cv_389 ON cv_389.customized_id=projects.id 
	LEFT JOIN (
	(SELECT cv_user.*, custom_values.customized_id FROM users cv_user
     JOIN custom_values ON
	 CAST(custom_values.value AS decimal(60,3)) = cv_user.id
      AND custom_values.customized_type='Project'
      AND custom_values.custom_field_id=388)
	) cv_388 ON cv_388.customized_id=projects.id 
	LEFT JOIN (
	(SELECT cv_user.*, custom_values.customized_id FROM users cv_user
     JOIN custom_values ON
	 CAST(custom_values.value AS decimal(60,3)) = cv_user.id
      AND custom_values.customized_type='Project'
      AND custom_values.custom_field_id=353)
	) cv_353 ON cv_353.customized_id=projects.id 
	LEFT JOIN (SELECT wiki.project_id, SUM(wiki_attached.filesize) AS filesize FROM wikis wiki JOIN wiki_pages pages ON pages.wiki_id = wiki.id JOIN attachments wiki_attached ON (wiki_attached.container_id = pages.id AND wiki_attached.container_type = 'WikiPage') GROUP BY wiki.project_id) wiki ON projects.id = wiki.project_id LEFT JOIN (SELECT wp.project_id, SUM(wp_attached.filesize) AS filesize FROM work_packages wp JOIN attachments wp_attached ON (wp_attached.container_id = wp.id AND wp_attached.container_type = 'WorkPackage') GROUP BY wp.project_id) wp ON projects.id = wp.project_id LEFT JOIN repositories repos ON repos.project_id = projects.id LEFT JOIN (        SELECT project_id, MAX(updated_at) latest_activity_at
        FROM (        SELECT project_id project_id, MAX(work_packages.updated_at) updated_at
        FROM work_packages

        WHERE work_packages.updated_at IS NOT NULL
        GROUP BY project_id
 UNION ALL         SELECT id project_id, MAX(projects.updated_at) updated_at
        FROM projects

        WHERE projects.updated_at IS NOT NULL
        GROUP BY project_id
 UNION ALL         SELECT project_id project_id, MAX(changesets.committed_on) updated_at
        FROM repositories
                LEFT OUTER JOIN changesets
        ON repositories.id =
           changesets.repository_id

        WHERE changesets.committed_on IS NOT NULL
        GROUP BY project_id
 UNION ALL         SELECT project_id project_id, MAX(news.updated_at) updated_at
        FROM news

        WHERE news.updated_at IS NOT NULL
        GROUP BY project_id
 UNION ALL         SELECT project_id project_id, MAX(wiki_pages.updated_at) updated_at
        FROM wikis
                LEFT OUTER JOIN wiki_pages
        ON wikis.id =
           wiki_pages.wiki_id

        WHERE wiki_pages.updated_at IS NOT NULL
        GROUP BY project_id
 UNION ALL         SELECT project_id project_id, MAX(messages.updated_at) updated_at
        FROM forums
                LEFT OUTER JOIN messages
        ON forums.id =
           messages.forum_id

        WHERE messages.updated_at IS NOT NULL
        GROUP BY project_id
 UNION ALL         SELECT project_id project_id, MAX(time_entries.updated_at) updated_at
        FROM time_entries

        WHERE time_entries.updated_at IS NOT NULL
        GROUP BY project_id
 UNION ALL         SELECT project_id project_id, MAX(meetings.updated_at) updated_at
        FROM meetings

        WHERE meetings.updated_at IS NOT NULL
        GROUP BY project_id
 UNION ALL         SELECT project_id project_id, MAX(budgets.updated_at) updated_at
        FROM budgets

        WHERE budgets.updated_at IS NOT NULL
        GROUP BY project_id
) activity
        GROUP BY project_id
) activity ON projects.id = activity.project_id WHERE (projects.active IN ('t')) 
	
	ORDER BY     
	cv_353.lastname asc
,   cv_353.firstname asc
,	cv_353.id asc
,	cv_389.lastname asc
,   cv_389.firstname asc
,	cv_389.id asc
,   cv_388.lastname asc
,   cv_388.firstname asc
,	cv_388.id asc
	,
	"projects"."id" DESC LIMIT 250 OFFSET 1
```

The main blocker currently is the structure of the `CustomFieldOrder` of the `ProjectQuery` and the `Query` (for work package) class. There is one of this for each and they both rely on the `CustomField::OrderStatements` module. The structure would have to be reworked for all custom fields and ordering as well as grouping will have to looked after.

Work packages are affected in the same way (at least on community). At least the solution of using a `LEFT JOIN` as done in the last projects example works to remove the problem same as for projects.