Search filter in the theme selection menu fails to load sites

[weiyiwang-dotcms]

Parent Issue

No response

Problem Statement

When searching for sites using the search bar in the theme selection menu, some results do not show. This affects all customers who have more than 10 sites and themes saved for specific sites.

Steps to Reproduce

1. Spin up docker image for v23.10.24
2. Create 11 sites
3. Go to the template page and select a template
4. open the browser dev tools
5. Open the theme selection menu. In the network tab, of the developer tools, you will see that the initial request to load the first 10 sites works.
6. Save the response for the initial load of the sites list
7. Parse the hostnames in the response to determine which of the sites is missing
8. Search for the missing site in the search bar that opens when you click the dropdown in the theme selection menu
9. There will be no results returned, and the developer tools will show a 500 error
10. dotCMS logs also report an error with the pgsql queries. [11/04/24 16:12:05:503 UTC] ERROR business.HostFactoryImpl: An error occurred when searching for Sites based on the following criteria: filter[ abc ], condition[ cvi.live_inode IS NOT NULL ], showSystemHost [ true ]: ERROR: syntax error at or near "NULLORDER" Position: 307{ "SQL": ["SELECT c.inode FROM contentlet c INNER JOIN identifier i ON c.identifier = i.id AND i.asset_subtype = 'Host' INNER JOIN contentlet_version_info cvi ON c.inode = cvi.working_inode WHERE cvi.identifier = i.id AND LOWER(contentlet_as_json->'fields'->'hostName'->>'value' ) LIKE ? AND cvi.live_inode IS NOT NULLORDER BY CASE WHEN LOWER(contentlet_as_json->'fields'->'hostName'->>'value' ) = ? THEN 0 ELSE 1 END"],

Acceptance Criteria

Allow the site selector in the theme selection menu load and select any site, not just the first ten sites that load.

dotCMS Version

23.10.24

Proposed Objective

Technical User Experience

Proposed Priority

Priority 2 - Important

External Links... Slack Conversations, Support Tickets, Figma Designs, etc.

https://dotcms.zendesk.com/agent/tickets/115862 https://dotcms.slack.com/archives/CSHTYUR7H/p1712853191836409

Assumptions & Initiation Needs

No response

Quality Assurance Notes & Workarounds

No response

Sub-Tasks & Estimates

No response