How to query two collections at once?

[hdodov]

I have two collections with date fields. I want to present a paginated view where I have results from both, sorted by date. Using the Mongoose model directly, I could get halfway there like so:

const result = await payload.db.collections.collection_one.aggregate([
  {
    $unionWith: {
      coll: "collection_two",
    },
  },
  {
    $sort: {
      date: 1,
    },
  },
]);

This returns combined results and sorted, but the downside is that I can't use depth to populate relationship fields. Also, localized fields are not processed and are returned with their values in all languages.

How do I handle this?

[hdodov]

I dug around and found out that Payload resolves relationships and localization in afterRead(), then figured out how to use the mongoose-aggregate-paginate-v2 plugin (which Payload internally uses) to add the pagination. Here's what I got:

import mongoose from "mongoose";
import mongooseAggregatePaginate from "mongoose-aggregate-paginate-v2";
import payload from "payload";
import { Endpoint } from "payload/config";
import { afterRead } from "payload/dist/fields/hooks/afterRead";
import { PayloadRequest } from "payload/types";

async function getAggregatedDocuments({
  req,
  page,
  limit,
}: {
  req: PayloadRequest;
  page: number;
  limit: number;
}) {
  let model = payload.db.collections["my-model"];
  if (!model) {
    const schema = payload.db.collections["collection_one"].schema.clone();
    schema.plugin(mongooseAggregatePaginate);
    model = mongoose.model("my-model", schema, "collection_one");
    payload.db.collections["my-model"] = model;
  }
  const myAggregate = model.aggregate([
    {
      $unionWith: {
        coll: "collection_two",
      },
    },
    {
      $sort: {
        date: -1,
      },
    },
  ]);
  // @ts-ignore
  const result = await model.aggregatePaginate(myAggregate, {
    page,
    limit,
  });
  result.docs = await Promise.all(
    result.docs.map(async (doc) =>
      //@ts-ignore
      afterRead({
        collection:
          payload.collections[
            doc.fieldThatOnlyCollectionOneHas
              ? "collection_one"
              : "collection_two"
          ].config,
        context: {},
        doc,
        findMany: true,
        global: null,
        locale: req.locale,
        overrideAccess: true,
        req,
      })
    )
  );
  return result;
}

export const myEndpoint: Endpoint = {
  path: "/my-endpoint",
  method: "get",
  handler: async (req, res, next) => {
    // ...
    return res.status(200).send(
      await getAggregatedDocuments({
        req,
        limit,
        page,
      })
    );
  },
};

It appears to work great!

[hdodov]

…but AWS DocumentDB doesn't support $unionWith, so if you use that, you're screwed. :)

[DanRibbens]

This is the use-case for the @payloadcms/plugin-search package.

It handles it by creating an extra collection and writing all the changes for the fields you have configured to be indexed on your collections. This gives you a single collection to query to that then relates to the matching documents.

The other alternative is to use a search service like Algolia or similar. We don't have an official plugin for this yet, but it can be handled in collection hooks and I'm sure there are exaxmples to be found.

Hopefully one of these options is good for your requirements. Let me know what you end up going with or if you have any feedback about othe plugin!