Sort on multiple block properties

[arrows]

Hi all,

I am trying to sort the results of a query, but can’t get it to work for multiple properties.

This works for sorting on ‘topic’:

;Query 1
:query [:find ?p-name (pull ?b [*])
      :keys topic b
      :where
        [?details :block/properties ?details-props]
        [(get ?details-props :topic) ?topic]
        [(= ?topic "active")]
        [?details :block/page ?page]
        [?b :block/path-refs ?page]
        [?page :block/name ?p-name]
        [?b :block/marker ?marker]
        [(contains? #{"TODO" "DOING"} ?marker)]]
:result-transform
(fn [result]
  (map
    (fn [r]
      (update (:b r) :block/properties
        (fn [p]
          (-> p
            (assoc "topic" (:topic r))
            (assoc "deadline" (:block/deadline (:b r)))
            (assoc "scheduled" (:block/scheduled (:b r)))
            (assoc "priority" (:block/priority (:b r)))
    ))))
    (sort-by :topic result)
))

How to sort on priority and deadline as well?
Tried to move sorting to the top before trying multiple sorts, but it doesn’t work - what is the missing part?

;Query 2
:query [:find ?p-name (pull ?b [*])
      :keys topic b
      :where
        [?details :block/properties ?details-props]
        [(get ?details-props :topic) ?topic]
        [(= ?topic "active")]
        [?details :block/page ?page]
        [?b :block/path-refs ?page]
        [?page :block/name ?p-name]
        [?b :block/marker ?marker]
        [(contains? #{"TODO" "DOING"} ?marker)]]
:result-transform
(fn [result]
  (sort-by (fn [s] (get-in s [:block/properties :topic]))
    (map
      (fn [r]
        (update (:b r) :block/properties
          (fn [p]
            (-> p
              (assoc "topic" (:topic r))
              (assoc "deadline" (:block/deadline (:b r)))
              (assoc "scheduled" (:block/scheduled (:b r)))
              (assoc "priority" (:block/priority (:b r)))
      ))))
      result
    )
))

[mentaloid]

Welcome. Check these threads:

• Unite different :result-transform’s.
• Sort by multiple properties
  • When sorting, you may need to use :block/properties-text-values

@arrows

it doesn’t work

• Please be more specific on what happens.
• Some example blocks would also help.

[arrows]

Thanks for the hints! I tried the following:

This query orders nicely on topic:

:query [:find ?p-name (pull ?b [*])
      :keys topic b
      :where
        [?details :block/properties ?details-props]
        [(get ?details-props :topic) ?topic]
        [(= ?topic "active")]
        [?details :block/page ?page]
        [?b :block/path-refs ?page]
        [?page :block/name ?p-name]
        [?b :block/marker ?marker]
        [(contains? #{"TODO" "DOING"} ?marker)]]
:result-transform
(fn [result]
  (map
    (fn [r]
      (update (:b r) :block/properties
        (fn [p]
          (-> p
            (assoc "topic" (:topic r))
            (assoc "deadline" (:block/deadline (:b r)))
            (assoc "scheduled" (:block/scheduled (:b r)))
            (assoc "priority" (:block/priority (:b r)))
    ))))
    (sort-by :topic result)
))

like so:

I like to add priority and deadline:

:result-transform
(fn [result]
  (map
    (fn [r]
      (update (:b r) :block/properties
        (fn [p]
          (-> p
            (assoc "topic" (:topic r))
            (assoc "deadline" (:block/deadline (:b r)))
            (assoc "scheduled" (:block/scheduled (:b r)))
            (assoc "priority" (:block/priority (:b r)))
    ))))
    (sort-by (fn [s] (get-in s [:block/properties-text-values :deadline]))
      (sort-by (fn [s] (get-in s [:block/properties-text-values :priority]))
        (sort-by :topic result)
      )
    )
))

Ordering is only on topic (like the screenshot above).

Tried moving sort, but this doesn’t show entries in the table:

:result-transform
(fn [result]
(sort-by
  (juxt
    (fn [s] (get-in s [:block/properties-text-values :deadline]))
    (fn [s] (get-in s [:block/properties-text-values :priority]))
    :topic
    )
  )
  (map
    (fn [r]
      (update (:b r) :block/properties
        (fn [p]
          (-> p
            (assoc "topic" (:topic r))
            (assoc "deadline" (:block/deadline (:b r)))
            (assoc "scheduled" (:block/scheduled (:b r)))
            (assoc "priority" (:block/priority (:b r)))
    ))))
    result)
)

gives

Test data setup looks like this:
Page Logseq
Property topic with value active

Page Test topic
Property topic with value active

On journal 2025-10-17
#Logseq

• TODO een tweede taak
• TODO een vijfde taak
  • DEADLINE 20251121

#Test topic

• TODO #A een taak
• TODO een derde taak
  • DEADLINE 20251023
• TODO een vierde taak

On journal 2025-10-18
#Logseq

• TODO #A Zes

[mentaloid]

• Open the console (Ctrl + Shift + i) and check for errors.
  • In your last effort, the parentheses are wrong.
    • i.e. one parenthesis under :topic should be moved under result
      • The point is that sort-by should wrap map, just like fn wraps sort-by.
• sort-by is performed on the result of map. Therefore, r is no longer in scope, so :topic inside juxt should be read from :block/properties (where it went with assoc).
  • All other properties passed with assoc are also to be found only in the exact place passed (here under :block/properties).
• The order of lines inside juxt matters in the sorting order.
  • You may want to swap some lines with each-other.

[arrows]

Thank you mentaloid.

Updated the parenthesis - those are hard to see!

Read up on the other post about missing/empty values and added that in the mix as well. I am overlooking something..

:result-transform
(fn [result]
(sort-by
  (juxt
     (fn [s] (if
       (= "" (get-in s [:block/properties :topic] "")) 
       "Z" 
       (get-in s [:block/properties :topic])
     ))
     (fn [s] (if
       (= "" (get-in s [:block/properties :priority] "")) 
       "Z" 
       (get-in s [:block/properties :priority])
     ))
     (fn [s] (if
       (= "" (get-in s [:block/properties :deadline] "")) 
       "99991231" 
       (get-in s [:block/properties :deadline])
     ))
  )
  (map
    (fn [r]
      (update (:b r) :block/properties
        (fn [p]
          (-> p
            (assoc "topic" (:topic r))
            (assoc "deadline" (:block/deadline (:b r)))
            (assoc "scheduled" (:block/scheduled (:b r)))
            (assoc "priority" (:block/priority (:b r)))
    ))))
    result)
))

Above shows:

This is the same as removing the whole sort - so it doesn’t seem to match the data and thus doesn’t sort.

[arrows]

Found a way to view the raw data:

:view (fn [result] (for [r result] [:pre (pr-str r)]))

which shows:

There is a :block/properties with data such as topic set.
Why does this sort not work?

(sort-by
  (juxt
     (fn [s] (if
       (= "" (get-in s [:block/properties :topic] "")) 
       "Z" 
       (get-in s [:block/properties :topic])
     ))
     (fn [s] (if
       (= "" (get-in s [:block/properties :priority] "")) 
       "Z" 
       (get-in s [:block/properties :priority])
     ))
     (fn [s] (if
       (= "" (get-in s [:block/properties :deadline] "")) 
       "99991231" 
       (get-in s [:block/properties :deadline])
     ))
  )

[arrows]

Alright, found that the assoc in map used strings while the sort used keys, so fixed the assoc to used keys instead

It now starts sorting!

One thing to go is correctly sorting missing values:

• why do the entries with no priority sort before A (and B), idea is that Z is used
• why are no deadlines ordered before a deadline, idea is that 99991231 is used

[arrows]

Alright, rewrote the if’s in the sort to or, somehow that does work. And used a number for the default date instead of a string.

All working!

[mentaloid]

• or is a macro that expands to ifs, so it shouldn’t make a difference.
• Changing the date from string to number can make a difference.
  • When comparing a string to a number or a number to a string, the result can fail expectations.
• Now you have learnt much more than if you were given a working version since the beginning.
  • Nevertheless, future visitors would appreciate reading your final version.

[arrows]

Good to know that an or is rewritten. It is much more compact, so I do like it more in this case.

This is the ‘final’ version. Meanwhile I tweaked it more for my specific needs and use it together with other queries to setup a system to organize my work.

Query sorting on:

• topic, actually can’t be empty as the query looks for it,
• priority, assumes no priority is a BB priority which sorts it after B but before C, and
• deadline, no deadline means 9999-12-31.

#+BEGIN_QUERY
{:title "TODO's related to active topics"
:query [:find ?p-name (pull ?b [*])
      :keys topic b
      :where
        [?topics :block/properties ?topics-props]
        [(get ?topics-props :topic) ?topic]
        [(= ?topic "active")]
        [?topics :block/page ?page]
        [?b :block/path-refs ?page]
        [?page :block/name ?p-name]
        [?b :block/marker ?marker]
        [(contains? #{"TODO" "DOING"} ?marker)]]
:result-transform
(fn [result]
(sort-by
  (juxt
    (fn [s] (or (get-in s [:block/properties :topic]) "Z"))
    (fn [s] (or (get-in s [:block/properties :priority]) "BB"))
    (fn [s] (or (get-in s [:block/properties :deadline]) 99991231))
  )
  (map
    (fn [r]
      (update (:b r) :block/properties
        (fn [p]
          (-> p
            (assoc :topic (:topic r))
            (assoc :deadline (:block/deadline (:b r)))
            (assoc :scheduled (:block/scheduled (:b r)))
            (assoc :priority (:block/priority (:b r)))
    ))))
    result)
))
}
#+END_QUERY

The query then shows: