Search plugin totalDocs returns incorrect value

[TimLanzi]

Describe the Bug

When using the search plugin filtering by more than one field, results can return a totalDocs value higher than reality. Sometimes even higher than docs that exist.

Link to the code that reproduces this issue

https://github.com/TimLanzi/payload-search-bug

Reproduction Steps

1. Create a new Payload project with the website template
2. Create your account and seed your database
3. Create the category 'Dive' (I tried to pick a word that would show up in at least 2 of the posts) and add it to the post "Dollar and Sense"
4. Edit the file /src/app/(frontend)/search/page.tsx to add a filter on category.title:

  const posts = await payload.find({
    collection: 'search',
    depth: 1,
    limit: 12,
    ...(query
      ? {
          where: {
            or: [
              {
                title: {
                  like: query,
                },
              },
              {
                'meta.description': {
                  like: query,
                },
              },
              {
                'meta.title': {
                  like: query,
                },
              },
              {
                slug: {
                  like: query,
                },
              },
              + {
              +  'categories.title': { like: query },
              + },
            ],
          },
        }
      : {}),
  })

5. Add following JSX to the same file to view totalDocs on the page:

      <div className="container mb-16">
        <div className="prose dark:prose-invert max-w-none">
          <h1 className="sr-only">Search</h1>
          <Search />
        </div>
        + <div>{posts.totalDocs}</div>
      </div>

6. Go to search page on the local website and search 'dive'. You should see 2 results in the archive block but the totalDocs should read 3.

Which area(s) are affected? (Select all that apply)

plugin: search

Environment Info

Binaries:
  Node: 22.7.0
  npm: 10.8.2
  Yarn: 1.22.22
  pnpm: 9.8.0
Relevant Packages:
  payload: 3.0.2
  next: 15.0.3
  @payloadcms/db-postgres: 3.0.2
  @payloadcms/email-nodemailer: 3.0.2
  @payloadcms/graphql: 3.0.2
  @payloadcms/live-preview: 3.0.2
  @payloadcms/live-preview-react: 3.0.2
  @payloadcms/next/utilities: 3.0.2
  @payloadcms/payload-cloud: 3.0.2
  @payloadcms/plugin-form-builder: 3.0.2
  @payloadcms/plugin-nested-docs: 3.0.2
  @payloadcms/plugin-redirects: 3.0.2
  @payloadcms/plugin-search: 3.0.2
  @payloadcms/plugin-seo: 3.0.2
  @payloadcms/richtext-lexical: 3.0.2
  @payloadcms/translations: 3.0.2
  @payloadcms/ui/shared: 3.0.2
  react: 19.0.0-rc-65a56d0e-20241020
  react-dom: 19.0.0-rc-65a56d0e-20241020
Operating System:
  Platform: linux
  Arch: x64
  Version: #202405300957~1732141768~22.04~f2697e1 SMP PREEMPT_DYNAMIC Wed N
  Available memory (MB): 31971
  Available CPU cores: 8

[TimLanzi]

I just wanted to update with another finding. Looking at this again, it doesn't seem to be exclusive to the search plugin. In my project, I have a Resources collection with a similar structure to the search collection in my original issue. I created 2 resources and queried on the resources collection instead of the search collection. I searched "economic" where one resource has that keyword as a category and in the title and the other resource only has it as a category. Search results return 2 resources, but the pagination totalDocs returns 3.

[TimLanzi]

Another finding. I ran one of the raw queries to see if the rows returned would reflect results on the app. Here are the results:

It looks like it can return duplicate rows depending on categories/other relation fields.

[laurentiu-lazar]

Similar to this, is a simpler case, might be the same issue although I'm not sure

If you have a collection with an array field / relationship field with hasMany: true, and you want to search for documents having at least one item in the array field, the same document is counted multiple times if its field has multiple items.

payload.find's totalDocs counts duplicated docs when searching in array (tested in sqlite and postgres)

How to test:

Collection config

import { CollectionConfig } from 'payload';

export const Test: CollectionConfig = {
  slug: 'test',
  access: {
    read: () => true,
    create: () => true,
    update: () => true,
    delete: () => true
  },
  fields: [
    {
      name: 'array',
      type: 'array',
      fields: [
        {
          name: 'string',
          type: 'text'
        }
      ]
    }
  ]
};

Short test script

import { getPayload } from 'payload';
import config from '@payload-config';

async function run() {
  const payload = await getPayload({ config });
  await payload.delete({
    collection: 'test',
    where: {
      id: {
        exists: true
      }
    }
  });

  await payload.create({
    collection: 'test',
    data: {
      array: [
        {
          string: 'a'
        },
        {
          string: 'b'
        }
      ]
    }
  });

  const res = await payload.find({
    collection: 'test',
    where: {
      'array.string': {
        in: ['a', 'b']
      }
    }
  });
  console.log('Total docs', res.totalDocs); // 2 is returned even though only 1 document exists
}

run();

The documents returned in the .docs property are de-duplicated however this issue still messes with pagination

[r1tsuu]

All problems with totalDocs in Postgres were fixed in this PR https://github.com/payloadcms/payload/pull/11208. Feel free to open a new issue if it still persists.

[github-actions[bot]]

This issue has been automatically locked. Please open a new issue if this issue persists with any additional detail.