✨Discover storytelling in the AI age with Pixar's Matthew Luhn at Sanity Connect, May 8th—register now

Discussion of GROQ query optimization and potential for a "referencedBy" function

10 replies
Last updated: Mar 2, 2023
This isn't a question but a candid, open experiment that appears to have worked well.
GROQ has a
*references() * function but I wasn't aware of a referencedBy analogue to take advantage of the references being bidirectional.
I needed to determine the referenced documents of one type that two other documents of another identical type
share. In this case, two "weddings" having their list of "vendor" documents compared.
While my desired magic function doesn't seem to exist, I have seen clever approaches like
this and adapted it, so that it works in the one case I need it where

*[ _type == "vendor"]{
  _id,
  vendorName,
  "one":  count(*[_id==$weddingId1 && references(^._id)]),
  "two" : count(*[_id==$weddingId2 && references(^._id)])
}[one > 0 && two > 0].vendorName
and the params

{
  "weddingId1":"8bdbe960-3f74-45b9-9b8e-6269a2cae8e4",
  "weddingId2":"d83030c5-383a-4137-8385-4d305c3f3212"
  
}
give me, in Vision,

"result":[ 1 item
0:"Copper Rose"
]
The pivotal aspect is that the projection can be filtered, and even though I am used to always seeing numbers in those square brackets, it
is a filter and therefore permits some logic, just as a JavaScript filter might. Which is to say, "whatever passes this test goes through."
Part of the fun of GROQ though is practicing to where the syntax and concepts finally mesh in your head.

Once I had a feel that I was really targeting things, the
infinitely more clean, clear and elegant query below produces the same result:

*[
  _id in *[_id == $weddingId1].vendorList[]->_id &&
  _id in *[_id == $weddingId2].vendorList[]->_id
 ].vendorName
Mar 1, 2023, 11:11 PM
Nice! 🙂 I wonder if this is a common pattern and we should have a
referencedBy()
function.
I have a couple of comments about performance here, though.

One is that, unfortunately,
count(*[references()])
calls are not optimized internally, and will load all the documents into memory to count them. They can be very expensive to run if there are many documents matching. I don't think this is mentioned explicitly in the High Performance GROQ guide; if not, we should add it.
Secondly, your cleaner query is also a bit performance-challenged. But it can be optimized to avoid using `->`:

*[
  _id in *[_id == $weddingId1].vendorList[]._ref &&
  _id in *[_id == $weddingId2].vendorList[]._ref
].vendorName
You should never need to do
->_id
. The ID is always available as
_ref
.
Mar 2, 2023, 9:59 AM
Greetings
user L
!

I wonder if this is a common pattern and we should have a
referencedBy()
function.
That is a function that would be exciting, and, as hinted, feels natural in how it takes advantage of a core characteristic of the relationship of documents in Sanity.
l feel like I see multiple threads a month which are essentially asking about some variation of it.

In my studio I also get asked on a fairly regular basis to produce panels that indicate which documents call for the one being viewed.

If I am not mistaken, there are also at least two plugins built around that concept, and it might be a stepping stone for the people who want to choose for that kind of information to be brought inline in the field areas surrounding their inputs.

Syntactically it'd definitely be cleaner than starting from the opposite direction to create a projection that gets targeted.


count(*[references()])
calls are not optimized internally, and will load all the documents into memory to count them.
If it's any comfort, this was a Vision-only experiment because it was way easier doing it here than in my proxy database. I wouldn't be using it on a site, but duly noted, and a good reminder to be mindful and to re-read that article!

You should never need to do
->_id
. The ID is always available as
_ref
Oh my, that's another great reminder. It's the same piece of information, of course. I will blame the fact that my brain was stuck in dereferencing mode knowing I wanted to see what was inside the array of references 🙂
As far as that 'cleaner' query -- I did have a thought that it would be cool to have two arrays of ids and perform a "not" or "reverse" array::unique; one that was subtractive.

Which is to say, here are these two lists, and instead of removing the extra instance of what they both share, throw out everything that
isn't what they have in common, in order to reveal what they do.
How would you handle comparing the items of two docs like this?
Mar 2, 2023, 3:39 PM
Hmm, you could count?
count(*[_id == $weddingId1 || _id == $weddingId2].vendorList[]._ref) == 2
Mar 2, 2023, 3:43 PM
If it appears in both lists, the count should be 2
Mar 2, 2023, 3:43 PM
Oh,
vendorList[]
is an array… Uh, not without storing this in a temporary array
Mar 2, 2023, 3:44 PM
Temporary array meaning one of those invented keys one assigns to mean something?
Mar 2, 2023, 4:21 PM
Yeah, but it won't really help you here
Mar 2, 2023, 4:26 PM
Oh, no worries, I was just curious because I didn't remember hearing it called that before. Wanted to be on the same page 🙂
Mar 2, 2023, 4:50 PM
Thanks for all your insights, of course!
Mar 2, 2023, 4:50 PM
Any time
Mar 2, 2023, 4:50 PM

Sanity– build remarkable experiences at scale

Sanity is a modern headless CMS that treats content as data to power your digital business. Free to get started, and pay-as-you-go on all plans.

Was this answer helpful?