Group the query results by references that have a specific page referece

Questions & Help Queries
1

Hello,

I have a query that lists all WAITING tasks:

#+BEGIN_QUERY
{:title [:h4 "Waiting"]
 :query [:find (pull ?b [*])
     :where
     [?b :block/marker ?m]   
     [(contains? #{"WAITING"} ?m)]]
:result-transform (fn [result]
     (reverse(sort-by (fn [h]
             (get-in h [:block/updated-at])) result)))
:breadcrumb-show? false}
#+END_QUERY

Most of my waiting tasks have a reference to a responsible person name, e.g. [[DavideF]]. Each person name page has a reference “#person” in the first line of the page. E.g.:

WAITING [[DavideF]] to make a list of pressure requirements in the market

image
image322×142 3.49 KB

So I want my query results to be grouped by responsible names (those refs that have page ref “person”). Those results that do no have responsible person names should be grouped under a group “Unassigned”.
Is this possible to achieve?

Thanks a lot in advance for any help!

1 Like
2

Something like this:

#+BEGIN_QUERY
{:title [:h4 "Waiting"]
 :query [:find ?name (pull ?b [*])
   :keys name b
   :in $ %
   :where
     [?b :block/marker ?m]   
     [(contains? #{"WAITING"} ?m)]
     (or
       (assigned ?b ?name)
       (and
         (not (assigned ?b ?name))
         [(str "unassigned") ?name]
       )
     )
 ]
 :rules [
   [(assigned ?b ?name)
     [?b :block/refs ?ref]
     [?first :block/left ?ref]
     [?first :block/refs ?first-ref]
     [?first-ref :block/name "person"]
     [?ref :block/name ?name]
   ]
 ]
 :result-transform (fn [result]
     (map :b (sort-by :name
         (reverse (sort-by (fn [h]
             (get-in h [:b :block/updated-at])) result)))))
 :breadcrumb-show? false
}
#+END_QUERY
3

Thank you @mentaloid for your imput!

Unfortunately it still returns ungrouped list. The only difference I notice is that the list is now sorted by name refs. But also partailly - tasks with no assigned names are not sorted - they are scattered in the list.

4

Create a page named Unassigned, then try this query:

#+BEGIN_QUERY
{:title [:h4 "Waiting"]
 :query [:find ?p (pull ?b [*])
   :keys id b
   :in $ %
   :where
     [?b :block/marker ?m]   
     [(contains? #{"WAITING"} ?m)]
     (or
       (assigned ?b ?name)
       (and
         (not (assigned ?b ?name))
         [(str "unassigned") ?name]
       )
     )
     [?p :block/name ?name]
 ]
 :rules [
   [(assigned ?b ?name)
     [?b :block/refs ?ref]
     [?first :block/left ?ref]
     [?first :block/refs ?first-ref]
     [?first-ref :block/name "person"]
     [?ref :block/name ?name]
   ]
 ]
 :result-transform (fn [result]
     (map (fn [r] (:b
         (assoc-in r [:b :block/page] {:db/id (:id r) :block/name ""})
     )) result))
 :breadcrumb-show? false
 :group-by-page? true
}
#+END_QUERY
1 Like
5

I think we’re getting closer!
Now each task has it’s own group based on the person name, and each unsigned task gas a group Unassigned. The only issue now is that multiple tasks are not really grouped under the one same group, but there are many same groups. What I mean looks like this:

image
image1454×856 87.3 KB

6

I have made a change in the above query. Try the new version.

1 Like
7

Magnificent. Works exactly as needed.
Thank you so much!

8

@mentaloid one more question. Is it possible to adjust the query so that instead of the single reference “person” we could use several references with OR logic. E.g, to group the tasks based on references that that have references to either “person” or “assignee”?

9
  • One way is by replacing this line:
    [?first-ref :block/name "person"]
    with these ones:
    (or
  [?first-ref :block/name "person"]
  [?first-ref :block/name "assignee"]
)
  • However, semantically the concepts person and assignee are not at the same level:
    • Assignee refers to the role.
      • It could be a property, e.g. assignee:: [[DavideF]]
    • Person refers to the nature.
      • It could be the value of a property, e.g. type:: person
1 Like
10

Geez, what you proposed with "(or…) was my initial thought, but it did not work for some reason. But now I did it again and it works. Probably I had some mistakes in brackets or smthg…
Thanks