Sort in advanced query by multiple columns

I have the query below, which finds all open tasks scheduled for today, or with status NOW, and sorts them all by scheduled date ascending. How do I add to the query to put all tasks with priority A on the top of the list (sorted by scheduled date ascending), with all tasks with no priority beneath that (sorted by scheduled date ascending)?

#+BEGIN_QUERY
{ :query [:find (pull ?b [*])
  :in $ ?today ?tomorrow
  :where
    [?b :block/marker ?m]
    (not [(contains? #{"DONE" "CANCELED"} ?m)])
    [(get-else $ ?b :block/scheduled ?tomorrow) ?d]
    (or 
      [(= "NOW" ?m)]
      [(<= ?d ?today)])]
:inputs [:today :1d-after]
:result-transform (fn [result]
  (sort-by (fn [h]
    (get-in h [:block/scheduled])) result))
:breadcrumb-show? false}
#+END_QUERY
7 Likes

Yes, for the love of god, can we please have some examples of this. Functional programmers drive me nuts sometimes, I miss my ORDER BYs.

@JPMoo @mpj please try this result-transform and let me know if it works

:result-transform (fn [result]
  ((->> coll (sort-by (fn [h] (get h :block/priority "Z"))) (sort-by :block/scheduled)) result))

Edit: Never mind, what I posted is not correct. I will post a better solution if I get it working.

Nope, that did nothing (I saw that you are editing so might have used a prior version).

It did, however, get me to try and see if the intuitive way worked:

#+BEGIN_QUERY
{:title "πŸ“¬ **All Open Tasks**"
 :query [:find (pull ?b [*])
         :where
         (task ?b #{"TODO" "DOING" "NOW" "LATER"})

]
:result-transform (fn [result]
  (sort-by (fn [h]
     (get h :block/priority "Z")
     (get h :block/created-at)  
   ) result)
)
}
#+END_QUERY

It of course didn’t, but then I thought about how insane hardcore functional programmers are and reversed the parameters, and it worked:

#+BEGIN_QUERY
{:title "πŸ“¬ **All Open Tasks**"
 :query [:find (pull ?b [*])
         :where
         (task ?b #{"TODO" "DOING" "NOW" "LATER"})

]
:result-transform (fn [result]
  (sort-by (fn [h]
     (get h :block/created-at)  
     (get h :block/priority "Z")
   ) result)
)
}
#+END_QUERY

Can you explain what the ->> and coll thing is indented to do? I really love loqseq but datomic must be trying to win some kind of championship in worst documented thing of all time. The search on datomic.com fills me with rage, how can someone manifest something this obtuse?

Indeed what I posted is not correct, I was following this comment where sort-by is composed like this

(->> coll
     (sort-by :first-name)
     (sort-by :last-name)
     (sort-by :age >))

I also tried this

:result-transform (fn [result]
  (->> result (sort-by (fn [h] (get h :block/priority "Z"))) (sort-by :block/scheduled))) 

but it didn’t work.

I don’t think that the sort-by in this case is the clojure sort-by, which has a composable signature. If it was, my solution above would not work. I think that the sort-by here being called here is from the Logseq DSL, maybe this stuff, not well versed enough in Clojure to understand.

These technologies are promising is many ways and there are things that appeal to me philosophically but the tooling and docs around all of this is absolutely awful.

@mpj @JPMoo Now I got a solution that actually works:

:result-transform (fn [result]
  (sort-by (juxt (fn [h] (get h :block/priority "Z")) (fn [i] (get i :block/scheduled))) result))

I got this from an answer in the discord channel.
It definitely works this time.

2 Likes

Excellent, thanks for sharing.

I finally got my deadline drive task resurfacing work the way I want it, order by deadline first, then for the same day, order by priority.

Sharing my default queries here.

:default-queries
 {:journals [
   {:title "πŸ”¨ DAILY"
    :query [:find (pull ?h [*])
            :where
            [?h :block/marker ?marker]
            [(contains? #{"NOW" "DOING", "TODO"} ?marker)]
            [?t :block/name "daily"]
            [?h :block/refs ?t]
            [?h :block/page ?p]
            [?p :block/journal? true]
           ]
    :inputs []
    :result-transform (fn [result] (sort-by (fn [h] (get h :block/deadline)) result))
    :collapsed? false}

   {:title "πŸ”¨ NOW"
    :query [:find (pull ?h [*])
            :where
            [?h :block/marker ?marker]
            [(contains? #{"NOW" "DOING"} ?marker)]
            (not [?t :block/name "daily"]
            [?h :block/refs ?t])
            [?h :block/page ?p]
            [?p :block/journal? true]
           ]
    :inputs []
    ;:result-transform (fn [result] (sort-by (fn [h] (get h :block/deadline)) result))
    :result-transform (fn [result]
     (sort-by (juxt (fn [h] (get h :block/deadline)) (fn [i] (get i :block/priority "Z"))) result))
    :collapsed? false}

   {:title "πŸ“… NEXT"
    :query [:find (pull ?h [*])
            :in $ ?next
            :where
            [?h :block/marker ?marker]
            [(contains? #{"LATER" "TODO"} ?marker)]
            (not [?t :block/name "daily"]
            [?h :block/refs ?t])
            [?h :block/page ?p]
            [?p :block/journal? true]
            [?h :block/deadline ?d]
            [(< ?d ?next)]
           ]
    :inputs [:7d-after]
    ;:result-transform (fn [result] (sort-by (fn [h] (get h :block/deadline)) result))
    :result-transform (fn [result]
     (sort-by (juxt (fn [h] (get h :block/deadline)) (fn [i] (get i :block/priority "Z"))) result))
    :collapsed? false}

   {:title "πŸ“… WAITING"
    :query [:find (pull ?h [*])
            :in $ ?next
            :where
            [?h :block/marker ?marker]
            [(contains? #{"WAITING"} ?marker)]
            [?h :block/page ?p]
            [?p :block/journal? true]
            [?h :block/deadline ?d]
            [(< ?d ?next)]
           ]
    :inputs [:7d-after]
    ;:result-transform (fn [result] (sort-by (fn [h] (get h :block/deadline)) result))
    :result-transform (fn [result]
     (sort-by (juxt (fn [h] (get h :block/deadline)) (fn [i] (get i :block/priority "Z"))) result))
    :collapsed? true}

   {:title "πŸ“… BACKLOG"
    :query [:find (pull ?h [*])
            :in $ ?next
            :where
            [?h :block/marker ?marker]
            [(contains? #{"LATER" "TODO"} ?marker)]
            (not [?t :block/name "daily"]
            [?h :block/refs ?t])
            [?h :block/page ?p]
            [?p :block/journal? true]
            [?h :block/deadline ?d]
            [(>= ?d ?next)]
           ]
    :inputs [:7d-after]
    ;:result-transform (fn [result] (sort-by (fn [h] (get h :block/deadline)) result))
    :result-transform (fn [result]
     (sort-by (juxt (fn [h] (get h :block/deadline)) (fn [i] (get i :block/priority "Z"))) result))
    :collapsed? true}

   {:title "πŸ“… WITHOUT DEADLINE"
    :query [:find (pull ?h [*])
            :where
            [?h :block/marker ?marker]
            [(contains? #{"TODO" "LATER" "DOING" "NOW" "WAITING"} ?marker)]
            (not [?h :block/deadline _])
            [?h :block/page ?p]
            [?p :block/journal? true]
            (not [?t :block/name "daily"]
            [?h :block/refs ?t])
            (not [?t :block/name "kaizen"]
            [?h :block/refs ?t])
           ]
    :inputs []
    :result-transform (fn [result] (sort-by (fn [h] (get h :block/id "Z")) result))
    :collapsed? true}
 ]}
1 Like

Rather than using juxt and having to pass in multiple functions to get a vector of the results back, you could just use a single function that returns a vector directly:

(sort-by (fn [r] [(get r :block/deadline) (get r :block/priority "Z")]) result)