Nested queries, or queries within queries

Explain the problem as you see it

Just to be clear from the outset, this isn't really a request to embed queries inside other queries, rather it is a request for a certain type of query functionality that, given Tana's current queries, is most readily described in that way.

Sometimes I would like to be able to search for nodes whose field values themselves meet certain criteria, i.e. find some nodes that meet one criteria, and then further filter the results based on the properties of the field values of those nodes.

At other times I might want to search for nodes that meet a criteria, but then return the value of a particular field of those nodes.

Although the two case are different, they strike me as related: in both cases first a set of nodes is queried, and then a second action is taken based on the field values of the first query; in the latter case the field values are themselves returned as the end result of the query; in the former case the query is further refined based on the properties of the field values.

Why is this a problem for you?

This idea was prompted first by two different users on Slack asking within a few days of one another if this functionality was already possible. Both their use cases struck me as quite compelling. Those comprise the first type of case described above. Then I myself realised that I wanted to perform a search that wasn't yet possible. That was the impetus for the second type of case described above.

Type One

Sven Bendel wanted to be able to find only tasks assigned to projects with a certain property. So if you have a #task tag with a >Project field, which is an instance field for tag #project which has a >Status field, he wanted to be able to find only #task instances whose >Project value was a #project instance whose >Status was "Focus".

Jessica Peters-Banton wanted to be able to find tasks whose current status was blocked, but where the blocking task had status done. In other words, find tasks currently marked as blocked which might actually no longer be blocked. If we have a #task tag, with a >Status field and a >Blocked by field (an instance field for #task), then she wanted to be able to search for all #task instances with a >Status of "Blocked" and a >Blocked by value being a #task instance whose status was "Done".

Both use cases strike me as compelling. They seem to be genuinely useful types of search that many users might want to perform. And in both cases they articulated what they wanted to do as embedding another query inside an existing query where a field value would normally be specified. Although I don't believe that would be a good way to actually implement this feature, I think it is telling that that is how users conceptualised what they wanted to do.

Type Two

I have a #book tag with a >Topics field (an instance field for #topic). I generally create instances of this tag when I buy a book, but I don't necessarily read a book the same year I bought it. I also have #reading log tag, where I note on my #day nodes what I read that day, and that tag has a >Book field which is an instance field for #book tag. Let's say I wanted to see what books I read in a certain year, or which topics had preoccupied me. As things stand this is not possible. I can query all the #reading log instances from a particular year, but as each book may have a dozen or more reading logs associated with it that is not a very clean way to quickly see the books that I read, and there doesn't seem to be any way from there to see the topics.

What I'd like to be able to do is to run a query that returned all the #book instances that were the value of a >Book field of a #reading log instance from this year. I'd also like to be able to take this a step further and run a query that returned all the #topic instances that were the value of a >Topic field of a #book instance that was the value of a >Book field of a #reading log instance from this year.

The most natural way to describe these sorts of queries is as queries on queries (on queries), although I want to reiterate that that does not strike me as a good UI.

Suggest a solution

If I could suggest a solution to this I'd be expecting a job offer from Tana!

But for type one case perhaps a WHERE VALUE field value operator might work in conjunction with dot notation:

#task
>Project      WHERE VALUE.Status      Focus

For type two cases, perhaps something like

#book    WHEN VALUE IN    #reading log
                          >Book      VALUE

and

#topic   WHEN VALUE IN    #book
                          >Topic     WHEN VALUE IN      #reading log
                                                        >Book      VALUE.Topic

Obviously none of these suggestions are particularly user friendly, and the implementation of such a feature depends not merely on the back-end capabilities but also a user friendly way of accessing those capabilities. It's clearly a difficult UX and UI problem to solve, but I think there is a clear use case for query features such as these.