How to get just the most recent of all documents

user_1728079 0
Viewed 1513

In sanity studio you get a nice list of the most recent version of all your documents. If there is a draft you get that, if not, you get the published one.

I need the same list for a few filters and scripts. The following groq does the job but is not very fast and does not work in the new API (v2021-03-25).

*[
  _type == $type &&
  !defined(*[_id == "drafts." + ^._id])
]._id

A way around the breaking changes in the API is to use length() = 0 in place of !defined() but that makes an already slow query 10-20 X slower.

Does anyone know a way of making filters that consider only the latest version?

Edit: An example where I need this is if I want to see all documents without any categories. Regardless whether it is the published document or the draft that has no categories it shows up in a normal filter. So if you add categories but don't immediately want to publish it will be confusing in the no-categories-list. ,'-)

groq sanity
2 Answers

100 X improvement on API v2021-03-25

The only way I was able to solve this with speed was to first make a projection of the sub-query so it doesn't run once for every non-draft. Then I thought, why not project both sets and then figure out the overlap, and that was even faster! It runs more than 10 x faster than possible on API v1 and 100 x faster than any suggestions for new API.

{
  'drafts': *[ _type == $type && _id in path("drafts.**") ]._id,
  'published': *[ _type == $type && !(_id in path("drafts.**"))]._id,
}
{
  'current': published[ !("drafts." + @ in ^.drafts) ] + drafts
}
  1. First I get both drafts and non-drafts and "store" it in this projection, like a variable--ish
  2. Then I start with my non-drafts - published
  3. And filter out any that has a counterpart in my drafts "variable"
  4. Lastly I add all drafts to the my list of filtered non-drafts
user_17280790

Overall I think you're on the right track. Some ideas to help you out:

  1. Drafts are always fresher and newer than published documents, so if a given doc's id in path("drafts.**"), that's already the last updated one.
  2. Knowing the above allows you to skip the defined(*[_id == ...]) part of the query for drafts, speeding up your execution
  3. As drafts are already included, we can exclude published documents with a draft (defined(*[_id == "drafts." + ^._id][0]))
  4. Notice I added a [0] to the end of the query to pick only the first element that matches. This will improve performance slightly.
  5. For getting only documents that have no categories, use count(categoriesField) < 1
  6. Order documents with | order(_updatedAt desc) to get the freshest documents first
  7. And paginate your request to reduce the payload and speed things up.

Here's a sample query applying these principles (I haven't ran it, you may have to do some adjustments there):

*[
  _type == $type &&
  // Assuming you only want those without categories:
  count(categories) < 1 &&
  (
    // Is either a draft -> drafts are always fresher
    _id in path("drafts.**") ||
    // Or a published document with no draft
    !defined(*[_id == "drafts." + ^._id][0])

    //  with the check above we're ensuring only
    // published documents run the expensive defined query
  )
]
// Order by last updated
| order(_updatedAt desc)
// Paginate for faster queries
[$paginationStart..$paginationEnd]
// Get only the _id, assuming that's what you want
._id

Hope this helps

user_104336470
Related