Simplify your stack and build anything. Or everything.
Build tomorrow’s web with a modern solution you truly own.
Code-based nature means you can build on top of it to power anything.
It’s time to take back your content infrastructure.

How to query two collections at once?

default discord avatar
hdodov12 months ago
4 2

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?

  • Selected Answer
    discord user avatar
    DanRibbens
    12 months ago

    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!

  • default discord avatar
    hdodov12 months ago

    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!

    1 reply
    default discord avatar
    hdodov12 months ago

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

Star on GitHub

Star

Chat on Discord

Discord

online

Can't find what you're looking for?

Get dedicated engineering support directly from the Payload team.