[5.x]: CraftCMS GraphQL Search with orderBy "score" and "limit" Causes Incorrect Results

[arctouch-robsondejesus]

What happened?

Hi CraftCMS team!
I'm running into a puzzling issue with GraphQL search queries, and I would love some guidance.

The issue is when I combine orderBy: "score DESC" with site: "" (multi-site search), limit, and offset, pagination returns inconsistent/incorrect results.
However, removing the limit or the "score DESC" parameters makes it work correctly.

Working Scenarios:
✅ orderBy: "score DESC" + site: "" + no limit → Returns all 55 results correctly
✅ site: "" + limit + offset + no orderBy → Returns correct 6 results per page|

Broken Scenario:
❌ orderBy: "score DESC" + site: "" + limit: 6 + offset: 0 → Returns only a total of 3 results (expected 6 per page)

Is this a known issue with multi-site search + score ordering + pagination?
Is there a workaround that allows me to keep relevance sorting (orderBy: "score DESC") while using pagination?
Should I file a bug report, or am I missing something in how this combination should work?
Any insights or suggestions would be greatly appreciated!

My Query:

query searchResults($searchTerm: String!, $offset: Int!, $limit: Int!) {
  results: entries(
    site: "*"
    section: ["or", "aboutUs", "careers", "careersPages", "work", "creativeContent", "contact", "consumerBrand", "corporatePublicAffairs", 
      "insights", "thoughtLeaders", "localPages", "newsroom", "pages", "privacyNotice", "regionHome", "responsibleDisclosurePolicy", "sectors", 
      "products", "advisory", "innovationIntelligence", "authors"]
    search: $searchTerm
    orderBy: "score DESC"
    limit: $limit
    offset: $offset
  ) {...}
}

Count Query that returns 55 results:

query searchCount($searchTerm: String!) {
  count: entryCount(
    site: "*"
    section: ["or", "aboutUs", "careers", "careersPages", "work", "creativeContent", "contact", "consumerBrand", "corporatePublicAffairs", 
      "insights", "thoughtLeaders", "localPages", "newsroom", "pages", "privacyNotice", "regionHome", "responsibleDisclosurePolicy", "sectors", 
      "products", "advisory", "innovationIntelligence", "authors"]
    search: $searchTerm
    orderBy: "score DESC"
  )
}

Multi-site setup with site: "*"
Search term: "sports" (wildcard search)
Expected: 6 results per page from total 55
Actual: 3 results on page 1

Craft CMS version

5.8.21

PHP version

8.2

Operating system and version

Linux Ubuntu 24.04 LTS

Database type and version

MySQL 8.0

Image driver and version

No response

Installed plugins and versions

"craftcms/aws-s3": "2.2.3",
"craftcms/ckeditor": "^4.11.0",
"craftcms/cms": "5.8.21",
"nystudio107/craft-seomatic": "5.1.19",
"putyourlightson/craft-amazon-ses": "3.1.0",
"spicyweb/craft-embedded-assets": "5.4.3",
"topshelfcraft/wordsmith": "5.0.0",
"vlucas/phpdotenv": "^5.6.1"

[linear]

CMS-1826

[brandonkelly]

I’m not able to reproduce that on my end. Can you send your Composer files and a database backup into support@craftcms.com so we can look into it on your project?

[arctouch-robsondejesus]

@brandonkelly unfortunately, I'm not authorized to share the client's database backup.
This is the full query, provided in case it helps.

query searchResults($searchTerm: String!, $offset: Int!, $limit: Int!) {
  results: entries(
    site: "global"
    section: ["or", "aboutUs", "careers", "careersPages", "work", "creativeContent", "contact", "consumerBrand", "corporatePublicAffairs", 
      "insights", "thoughtLeaders", "localPages", "newsroom", "pages", "privacyNotice", "regionHome", "responsibleDisclosurePolicy", "sectors", 
      "products", "advisory", "innovationIntelligence", "authors"]
    search: $searchTerm
    orderBy: "score DESC"
    limit: $limit
    offset: $offset
    unique: true
  ) {
    id
    uri
    title
    siteHandle
    type: typeHandle
    ... on aboutUs_Entry {
      excerpt
      featuredImage: featuredImageAndVideo {
        ... on videoAndImage_Entry {
          background {
            title
            src: url
            extension
            type: mimeType
            kind
            focalPoint
            hasFocalPoint
          }
        }
      }
    }
    ... on careers_Entry {
      excerpt
      featuredImage: careersHeaderImages {
        title
        src: url
        extension
        type: mimeType
        kind
        focalPoint
        hasFocalPoint
      }
    }
    ... on careersPages_Entry {
      excerpt
      featuredImage {
        title
        src: url
        extension
        type: mimeType
        kind
        focalPoint
        hasFocalPoint
      }
    }
    ... on work_Entry {
      excerpt
      featuredImage {
        title
        src: url
        extension
        type: mimeType
        kind
        focalPoint
        hasFocalPoint
      }
      thumbnail {
        title
        src: url
        extension
        type: mimeType
        kind
        focalPoint
        hasFocalPoint
      }
    }
    ... on creativeContent_Entry {
      excerpt
      featuredImage {
        title
        src: url
        extension
        type: mimeType
        kind
        focalPoint
        hasFocalPoint
      }
    }
    ... on contact_Entry {
      excerpt
    }
    ... on consumerBrand_Entry {
      excerpt
      featuredImage {
        title
        src: url
        extension
        type: mimeType
        kind
        focalPoint
        hasFocalPoint
      }
    }
    ... on corporatePublicAffairs_Entry {
      id
      type: typeHandle
      title
      slug
      excerpt
      featuredImage {
        title
        src: url
        extension
        type: mimeType
        kind
        focalPoint
        hasFocalPoint
      }
    }
    ... on insights_Entry {
      excerpt
      topic: singleTopic {
        ... on topics_Entry {
          label: title
          slug
          featuredImage {
            title
            src: url
            extension
            type: mimeType
            kind
            focalPoint
            hasFocalPoint
          }
        }
      }
      featuredImage {
        title
        src: url
        extension
        type: mimeType
        kind
        focalPoint
        hasFocalPoint
      }
      thumbnail {
        title
        src: url
        extension
        type: mimeType
        kind
        focalPoint
        hasFocalPoint
      }
    }
    ... on thoughtLeaders_Entry {
      leaderType {
        slug
      }
      excerpt
      featuredImage {
        title
        src: url
        extension
        type: mimeType
        kind
        focalPoint
        hasFocalPoint
      }
      thumbnail {
        title
        src: url
        extension
        type: mimeType
        kind
        focalPoint
        hasFocalPoint
      }
    }
    ... on localPages_Entry {
      excerpt
      featuredImage {
        title
        src: url
        extension
        type: mimeType
        kind
        focalPoint
        hasFocalPoint
      }
    }
    ... on newsRoom_Entry {
      excerpt
      category: newsCategory {
        ... on newsCategory_Entry {
          label: title
          slug
          description
          featuredImage {
            title
            src: url
            extension
            type: mimeType
            kind
            focalPoint
            hasFocalPoint
          }
        }
      }
      featuredImage {
        title
        src: url
        extension
        type: mimeType
        kind
        focalPoint
        hasFocalPoint
      }
      thumbnail {
        title
        src: url
        extension
        type: mimeType
        kind
        focalPoint
        hasFocalPoint
      }
    }
    ... on pages_Entry {
      excerpt
      featuredImage {
        title
        src: url
        extension
        type: mimeType
        kind
        focalPoint
        hasFocalPoint
      }
    }
    ... on privacyNotice_Entry {
      excerpt
      featuredImage {
        title
        src: url
        extension
        type: mimeType
        kind
        focalPoint
        hasFocalPoint
      }
    }
    ... on regionHome_Entry {
      excerpt
      featuredImage {
        title
        src: url
        extension
        type: mimeType
        kind
        focalPoint
        hasFocalPoint
      }
    }
    ... on responsibleDisclosurePolicy_Entry {
      excerpt
      featuredImage {
        title
        src: url
        extension
        type: mimeType
        kind
        focalPoint
        hasFocalPoint
      }
    }
    ... on sectors_Entry {
      featuredImage {
        title
        src: url
        extension
        type: mimeType
        kind
        focalPoint
        hasFocalPoint
      }
    }
    ... on product_Entry {
      excerpt
      featuredImage {
        title
        src: url
        extension
        type: mimeType
        kind
        focalPoint
        hasFocalPoint
      }
    }
    ... on advisory_Entry {
      excerpt
      featuredImage {
        title
        src: url
        extension
        type: mimeType
        kind
        focalPoint
        hasFocalPoint
      }
    }
    ... on innovationIntelligence_Entry {
      excerpt
      featuredImage {
        title
        src: url
        extension
        type: mimeType
        kind
        focalPoint
        hasFocalPoint
      }
    }
    ... on authors_default_Entry {
      excerpt
      featuredImage {
        title
        src: url
        extension
        type: mimeType
        kind
        focalPoint
        hasFocalPoint
      }
      thumbnail {
        title
        src: url
        extension
        type: mimeType
        kind
        focalPoint
        hasFocalPoint
      }
    }
  }
}

[brandonkelly]

Would it be possible to at least share your Composer files and config/project/ folder, plus any custom plugins/modules?

[arctouch-robsondejesus]

Sure @brandonkelly
I'm going to send it to you by email right away

[arctouch-robsondejesus]

After further investigation, I found the actual root cause of this issue.

The entries in the sections consumerBrand, corporatePublicAffairs, creativeContent, advisory, and innovationIntelligence only exist in a single site (site ID 1 / global) in the elements_sites table.
However, the searchindex table contained orphaned rows for these entries pointing to 55 additional sites (site IDs 3–57) where the entries no longer exist.

For example, entry 322113 ("Sports & Entertainment Marketing" in consumerBrand) had:
1 row in elements_sites (site ID 1 -- correct)
388 rows in searchindex across site IDs 1, 3–57 (only site ID 1 is valid, the rest are orphaned)

Removing them manually resolved the search misresults

[brandonkelly]

@arctouch-robsondejesus Good catch! Craft 4.17.7 and 5.9.13 are out with fixes for that. The gc command has also been updated to prune out those orphaned rows.

Thanks for looking into it!