Hello! How can I sort items by property A THEN property B in a chain? Not only built-in properties like DEADLINE or SCHEDULED but also user properties? Also, how can I control sorting order (desc vs asc) individually?
Currently, here’s what’s helping me sort by p-score (priority score)
:result-transform (fn [result] (sort-by (fn [r] (get-in r [:block/properties :p-score]) ) > result))
…I’d like to then sort by deadline but in ascending order! How can I manage that chain in the :result-transform?
Thank you so much for any help.
I had been wondering about this for sometime. Most clojure examples about this problem, don’t really seem to work in Logseq. It may be due to my limited understanding of clojure, or some other restriction, I don’t know.
I had the stupid idea to just nest two sort by clauses. Not sure exactly what happens. But at least it does… something!
Two examples to illustrate.
Subpagina’s test > begin
:result-transform (fn [result]
(sort-by
(fn [r] (get-in r [:block/properties-text-values :datum-begin])) >
(sort-by (fn [r] (get-in r [:block/properties-text-values :test])) result)
)
)
Subpagina’s begin > test
:result-transform (fn [result]
(sort-by
(fn [r] (get-in r [:block/properties-text-values :test]))
(sort-by (fn [r] (get-in r [:block/properties-text-values :datum-begin])) > result)
)
)
I think that to sort firstly by :test
and when equal by inverse :datum-begin
, it is possible like this:
:result-transform (fn [result]
(sort-by
(juxt
(fn [r] (get-in r [:block/properties-text-values :test]))
(comp - (fn [r] (get-in r [:block/properties-text-values :datum-begin])))
)
result
)
)
So I thought that wasn’t working, but actually what wasn’t working is that the property can be empty or can not exist.
To get around that:
(fn [r]
(if
(= "" (get-in r [:block/properties :test] ""))
"9999-99-99"
(get-in r [:block/properties :test])
)
)
In the get-in
a default value of ""
is added. So if the property doesn’t exist the get-in
returns ""
If the property exists without a value, it will be ""
as well.
Then through the if, when the value is ""
replace it with a default value, in this case 9999-99-99
, else just return the actual value.
This seems to solve my problem. So now (comp - )
actually works. And that’s the more elegant option.
Edit: nope I have to get back to this actually.
Comp is showing some weird behavior.
I have a query like so (commented out some things as I try to figure out the problem)
#+BEGIN_QUERY
{:title [:b "Overzicht"]
:inputs [:query-page]
:query [:find (pull ?p [*])
:in $ ?page
:where
[?b :block/properties ?prop]
[(get ?prop :cluster) ?cluster]
[?b :block/page ?p]
(or
[(= ?cluster ?page)]
[(contains? ?cluster ?page)]
)
]
:result-transform (fn [result]
(sort-by
; (juxt
; (fn [r] (get-in r [:block/properties-text-values :type]))
(fn [r]
(if
(= "" (get-in r [:block/properties-text-values :datum-aanschaf] ""))
"1111-11-11"
(get-in r [:block/properties-text-values :datum-aanschaf])
)
)
; (fn [r] (get r :block/name))
; )
result
)
)
}
#+END_QUERY
Which gives me a sorting as expected:
However when I surround that with comp I get very weird results.
(comp - (fn [r]
(if
(= "" (get-in r [:block/properties-text-values :datum-aanschaf] ""))
"1111-11-11"
(get-in r [:block/properties-text-values :datum-aanschaf])
)
) )
Seems to only work correctly when things are numbers:
1 Like
Let’s try an explicit way with sort
:
:result-transform (fn [result]
(sort
(fn [l r]
(defn obj-prop [obj prop dflt] (get-in obj [:block/properties-text-values prop] dflt))
(defn compare-by-prop [l r prop dflt] (compare (obj-prop l prop dflt) (obj-prop r prop dflt)))
(def t (compare-by-prop l r :test))
(if (not= t 0) t (- (compare-by-prop l r :datum-begin)))
)
result
)
)
Can pass default values if needed.
EDIT: To make this work when the results are pages, add anywhere this comment: ;(sort-by
Changed :test
to :categorie
and :datum-begin
to :datum-aanschaf
for use in my query, but it seems to not sort at all.
Also checked the file to make sure there were no hidden sort properties. Even put the query in a fresh block.
Decided to go to a fully new graph.
And there the comp
solution also failed.
However the explicit sort does work there. Not sure what is different in my normal graph then. Except blocks vs. pages.
Full page from my new graph:
- Item 1
categorie:: cat 1
datum-aanschaf:: 2015-08-29
- Item 2
categorie:: cat 2
datum-aanschaf:: 2014-07-17
- Item 3
categorie:: cat 3
datum-aanschaf:: 2022-07-11
- Item 4
categorie:: cat 4
datum-aanschaf:: 2023-03-15
- Item 5
categorie:: cat 2
datum-aanschaf:: 2023-11-14
- Item 6
categorie:: cat 3
datum-aanschaf:: 2024-01-06
- Item 7
categorie:: cat 4
datum-aanschaf:: 2024-04-25
- Item 8
categorie:: cat 1
datum-aanschaf:: 2020-09-25
- Item 9
categorie:: cat 1
datum-aanschaf::
- query-table:: true
query-properties:: [:block :categorie :datum-aanschaf]
#+BEGIN_QUERY
{:title "Sorting"
:query [:find (pull ?b [*])
:where
[?b :block/properties ?prop]
[(get ?prop :categorie)]
]
:result-transform (fn [result]
(sort
(fn [l r]
(defn obj-prop [obj prop dflt] (get-in obj [:block/properties-text-values prop] dflt))
(defn compare-by-prop [l r prop dflt] (compare (obj-prop l prop dflt) (obj-prop r prop dflt)))
(def t (compare-by-prop l r :categorie))
(if (not= t 0) t (- (compare-by-prop l r :datum-aanschaf)))
)
result
)
)
}
#+END_QUERY
- query-table:: true
query-properties:: [:block :categorie :datum-aanschaf]
#+BEGIN_QUERY
{:title "Sorting"
:query [:find (pull ?b [*])
:where
[?b :block/properties ?prop]
[(get ?prop :categorie)]
]
:result-transform (fn [result]
(sort-by
(comp - (fn [r] (get-in r [:block/properties-text-values :datum-aanschaf] "")) )
result
)
)
}
#+END_QUERY
Query result:
Edit: confirmed. It breaks apart when using pages ![:woman_shrugging:t4: :woman_shrugging:t4:](https://discuss.logseq.com/images/emoji/twitter/woman_shrugging/4.png?v=12)
1 Like
Great ![:expressionless: :expressionless:](https://discuss.logseq.com/images/emoji/twitter/expressionless.png?v=12)
Seems the nested sort from my post seems to bypass this problem and deliver a correct result.
Which one? Could you post the full query that works correctly on your test graph?
Of course!
#+BEGIN_QUERY
{:title "Sorting"
:query [:find (pull ?p [*])
:where
[?p :block/properties ?prop]
[?p :block/name]
[(get ?prop :categorie)]
]
:result-transform (fn [result]
(sort-by
(fn [r] (get-in r [:block/properties-text-values :categorie]))
(sort-by (fn [r] (get-in r [:block/properties-text-values :datum-aanschaf])) > result)
)
)
}
#+END_QUERY
Produces the result as shown in my previous post.
For this I changed all blocks to a page with the 2 properties as page properties.
Rest of the page is empty.
1 Like