Advanced Query for property "due-by" between 7 and 30 days from now, sorted by due-date and priority

[FlorianF]

Super, thanks @Siferiax .
I added a new condition, that the block contains a property “status” of value “defacut”.

Have some questions:

1. the actual status in the property is “#defacut” but if I put that in the condition i get nothing;
2. there are several statuses i need to address like “#defacut”, “#determinat”, etc. do I have to write a “contains” condition for each status or I can group them in a single “contains” ?
   • [(contains? ?stat (or "defacut" "determinat")] doesn’t work, maybe i write it wrong;
   • (or [(contains? ?stat "defacut")] [(contains? ?stat "determinat")] ) doesn’t work either This Works, I had a typo in there, a missing closing round bracket ;

• question 1 still remains…

#+BEGIN_QUERY
{:title [:h3 "Soon... (7-30 days):"]
 :inputs [:+7d :+30d]
 :query [:find (pull ?b [*])
  :in $ ?start ?next
  :where
   [?b :block/properties ?prop]
   [(get ?prop :due-by) ?due] ;get the due-by property
   [(get ?prop :status) ?stat] ;get the status property
   (or [(contains? ?stat "defacut")] [(contains? ?stat "determinat")] )
   [?b :block/refs ?j] ;get the block references
   [?j :block/original-name ?journal] ;get the page name of the reference
   [(contains? ?due ?journal)] ;check that the reference is actually present in the property
   [?j :block/journal-day ?d] ;get the date of the journal page
   [(<= ?start ?d ?next)] ;check that the journal page is in range
 ]
 :breadcrumb-show? false
}
#+END_QUERY

[Siferiax]

We can pull out what is actually in the raw data of the property.
So for references (1 or more) it is actually #{"value"}
For example :status #{"defacut" "determinat"} (should you use both)
So the # is not part of it.

For contains? the syntax is (contains? coll key)
whereby coll (collection) would be #{"value"} and the key whatever value you which to check.
So it should be read as does coll contain key?
However in our case the status property is in fact a collection and not a single value. (even if it only has 1 reference, it would still be a collection of 1)

So the correct solution is the one you actually got to yourself!
(or [(contains? ?stat "defacut")] [(contains? ?stat "determinat")] )
Or collection ?stat contains the value “defacut” or collection ?stat contains the value “determinat”.

[Siferiax]

PS. should status always be 1 reference we can flip it around.

[?b :block/properties-text-values ?textprop] ;get plain text of the properties
[(get ?textprop :status) ?stat] ;get the status property
[(contains? #{"#defacut" "determinat"} ?stat)]

Now the # has to be included as we converted it to plain text.

Note that the due-by needs the :block/properties to work! Else we have to reconsider that part as well.

[FlorianF]

@Siferiax

[?b :block/properties-text-values ?textprop] ;get plain text of the properties
[(get ?textprop :status) ?stat] ;get the status property
[(contains? #{"#defacut" "determinat"} ?stat)]

status is always a single hashtag-value property. Thanks for the clarification as it adds to the knowledge embedded in this single query

#+BEGIN_QUERY
{:title [:h3 "Soon... (7-30 days):"]
 :inputs [:+7d :+30d]
 :query [:find (pull ?b [*])
  :in $ ?start ?next
  :where
   [?b :block/properties-text-values ?textprop] ;get plain text of the properties
   [(get ?textprop :status) ?stat] ;get the status property
   [(contains? #{"#defacut" "determinat"} ?stat)]
   [?b :block/properties ?prop]
   [(get ?prop :due-by) ?due] ;get the due-by property
   [?b :block/refs ?j] ;get the block references
   [?j :block/original-name ?journal] ;get the page name of the reference
   [(contains? ?due ?journal)] ;check that the reference is actually present in the property
   [?j :block/journal-day ?d] ;get the date of the journal page
   [(<= ?start ?d ?next)] ;check that the journal page is in range
   [(get ?prop :priority) ?stat] ;get the status property
 ]
 :breadcrumb-show? false
}
#+END_QUERY

I will have to add even more to it, like sorting the results by date-approaching combined with yet another property priority but I first have to figure out how to give priorities some order beyond that based on their name (because the first letter in the naming has nothing to do with the urgency level). If only properties could have sub-properties

[mentaloid]

@FlorianF

If only properties could have sub-properties

They can, but in their own page. Generally should not squeeze too much info in a single value. Use instead a reference to a page (or block) that properly describes the extra info. Advanced queries can follow the reference and use any part of that info.

[Siferiax]

@FlorianF

:inputs [:7d-after :30d-after]

That input is deprecated.
Please continue to use correct inputs as presented: :+7d and :+30d
https://docs.logseq.com/#/page/advanced%20queries/block/relative%20date%20inputs

[FlorianF]

ok, got it. Corrected above.

[FlorianF]

I am still not ready to use pages for anything else than dashboards and I don’t really want to mix metadata and dashboards in the same document. I could always write it as priority:: [Critical]([[A]]) and sort by [[A]] I guess…

[FlorianF]

@Siferiax

If I have several Tasks with multiple-values in the due-by:: property, is there a way to sort them by the last such value (2024-05-11 in your example)?

[Siferiax]

I honestly don’t know how
With regex maybe?

[FlorianF]

if due-by:: would be an array then such syntax as ?due-by[-1] would be its last element in the collection…

...
[(get ?prop :due-by) ?due]
...
:result-transform (fn [result]
                            (sort-by (fn [d]
                                       (get d ?due[-1] ) result))

Of course I have no idea what I am writing in there, just trying to convey the conceptual aspect

[FlorianF]

This is weird. I added the sort-by function to the :result-transform like so:

:result-transform (fn [result] (sort-by (fn [h] (get h ?d) ) result))
;  ?d -> from "[?j :block/journal-day ?d] ;get the date of the journal page"

and I have a single date wikilink value in that property, so no multiple-values like mentioned before, but it seems to sort by the literal value and not the actual date behind the wikilink (my Date preference is “[[Wednesday, 24.04.2024]]”).

If I clik in the results table the due-by column to reverse order I get confirmation that it does the sorting based on the literal text instead of the date as in “date-format”…

Edit: I modified the :result-transform to:

 :result-transform (fn [result] (sort-by (fn [h] (get h :block/journal-day) ) result))

but still nothing changes in the table-view:

• I wonder if this line works at all;
• if I order the due-by column in the table by clicking on its header I get an alphabetical sorting so I assume that the by-date sorting can only be done in the query while the actual table takes everything inside the cells as literals?

[FlorianF]

@FlorianF

Edit: I modified the :result-transform to:

 :result-transform (fn [result] (sort-by (fn [h] (get h :block/journal-day) ) result))

but still nothing changes in the table-view:

• I wonder if this line works at all;
• if I order the due-by column in the table by clicking on its header I get an alphabetical sorting so I assume that the by-date sorting can only be done in the query while the actual table takes everything inside the cells as literals?

I really hope you can find some time to answer some of this as it would help me understand this -potentially more dev-oriented- side of Logseq Queries (like Regex, for example)

[Siferiax]

A block doesn’t have an attribute :block/journal-day, there is no relationship between the two.
Can only accomplish with more advanced tinkering.
I’m off to bed now, but you can find an example here:
https://siferiax.github.io/#/page/66265648-109a-4321-a6f4-75c3ea4c3310
something similar is used in this topic:

Result transform conditional

Hi all, So I have this problem, and I’m not sure how to solve. Within my journal query, I have the following result -transform: :result-transform (fn [r] (map (fn [m] (assoc m :block/collapsed? true)) r)) What this does is that it collapses every block that results from the query. This way, I end up with a neat list - which is itself uncollapsed by default - containing all the tasks I want, where the tasks themselves are collapsed. However, the side-effect of this approach is that every ta…

And also yes, when clicking on the column header it will always sort by literal (ie column visible value) and not underlining data.

[FlorianF]

@Siferiax

And also yes, when clicking on the column header it will always sort by literal (ie column visible value) and not underlining data.

That is quite shocking to learn, because dates are the most sorted “thing” in a journal-based app. Then why let “EEEE, dd.MM.yyyy” as an option in Preferred Date Format id one will never be able to sort it easily without advanced queries and no less, even “more advanced thinkering” :-/ . That’s a huge oversight…

I’ll try to understand the linked topic…

[FlorianF]

@FlorianF

I’ll try to understand the linked topic…

Ok, I don’t :)) … but I wonder, if I already have the “date” in the ?d variable (which I compare with “?start” and "?next, why can’t I use that in the result-transform function?

[Siferiax]

@FlorianF

Then why let “EEEE, dd.MM.yyyy” as an option in Preferred Date Format id one will never be able to sort it easily without advanced queries and no less, even “more advanced thinkering” :-/ .

2 things.

1. user preference. Personally for readability I prefer date format EEE dd MMM yyyy myself (it isn’t officially supported even )
2. thinkering is only required as we’re talking about a property value which points to a journal page. When we talk about a block on a journal page it is much more straight forward.

@FlorianF

why can’t I use that in the result-transform function?

Because it is not something your query is currently returning as a result.
You return the block:

{:block/properties-text-values
 {:due-by "[[Friday, 26.04.2024]]", :status "#defacut"},
 :block/uuid #uuid "662f6f55-48f2-431c-a227-59f81b7df483",
 :block/properties
 {:due-by #{"Friday, 26.04.2024"}, :status #{"defacut"}},
 :block/left {:db/id 21},
 :block/refs [{:db/id 4} {:db/id 22} {:db/id 24}],
 :block/properties-order (:due-by :status),
 :block/format :markdown,
 :block/content
 "TODO Task to do\ndue-by:: [[Friday, 26.04.2024]]\nstatus:: #defacut",
 :db/id 41,
 :block/path-refs [{:db/id 4} {:db/id 21} {:db/id 22} {:db/id 24}],
 :block/parent {:db/id 21},
 :block/page {:db/id 21}, 
 :block/marker "TODO"}

Nowhere in this data is the information we need for sorting.
For that we would need the page data for that Friday date:

{:db/id 22,
 :block/created-at 1714384725767,
 :block/journal-day 20240426,
 :block/journal? true,
 :block/name "friday, 26.04.2024",
 :block/original-name "Friday, 26.04.2024",
 :block/updated-at 1714384725767,
 :block/uuid #uuid "662f6f55-8420-436c-9129-ded978361bc2"}

Here we find the :block/journal-day which can be used.
We just don’t have this information available to us in the block data. So we will need to add that.
Which is where my examples came in

So taking the query you posted earlier we can add to it for this purpose.

#+BEGIN_QUERY
{:title [:h3 "Soon... (7-30 days):"]
 :inputs [:-7d :+30d]
 :query [:find ?d (pull ?b [*])
  :keys date block ;bind :find output to keys: the ?d variable to date and the block data to block
  :in $ ?start ?next
  :where
   [?b :block/properties-text-values ?textprop] ;get plain text of the properties
   [(get ?textprop :status) ?stat] ;get the status property
   [(contains? #{"#defacut" "determinat"} ?stat)]
   [?b :block/properties ?prop]
   [(get ?prop :due-by) ?due] ;get the due-by property
   [?b :block/refs ?j] ;get the block references
   [?j :block/original-name ?journal] ;get the page name of the reference
   [(contains? ?due ?journal)] ;check that the reference is actually present in the property
   [?j :block/journal-day ?d] ;get the date of the journal page
   [(<= ?start ?d ?next)] ;check that the journal page is in range
;   [(get ?prop :priority) ?stat] ;get the status property
 ]
 :breadcrumb-show? false
 :result-transform (fn [result] (sort-by ;sort the result by the :date key through some functions
  (fn [r] (get-in r [:block/properties :date])) ;use the binding from the map below
  (map ;make a new map from the result set
    (fn [r]
      (update (:block r) :block/properties ;we're going to update the values for the block attribute :block/properties
        (fn [u] (assoc u :date (get-in r [:date]) ) ) ;putting the :date key/value pair from the results into the :block/properties attribute.
      ) 
    )
    result
  )
 ))
}
#+END_QUERY

(had to comment out the last line of the where as that is not a property I have in my test data )

[FlorianF]

@Siferiax

26.04.2024

Oh cmon, what is this :))) not much thinkering you sau?:))

I copy/pasted the code and it doesn’t sort it for me …

If it was up to me, I would take the “literal” [[Friday, 26.04.2024]], extract the numbers with regex and reverse those ($3$2$1) and use that but I wouldn’t know to do that in closure…

Edit: if i use List View it shows the correct order, if I use Table View it’s still nok as it orders “literal” by the day of week starting word of the due-date column. Query: if I clicked on the column header to sort it ascending or descending, does it write that somewhere ins the internal db so that i “remmebers” it? Otherwise I can’t understand why List View would work but not Table View …

Edit2: ok, deleted that block and created a new block with the same query and it works this time. Nevertheless, the level of proficientcy one is supposed to have to just look for a property value, match it with something that logseq internally has as a value (journal-day) and sort it the way you showed is astonishing to me. Kudos to you that you can do it but I don’t think i’ll be ever able to learn advanced queries at such high level and many more like me :(. It’s a pitty there isn’t a more friendly way to do advanced queries that is intuitive and doesn’t require to learn a new programming language :-/
… and with that rule you commented I wanted to have a next level sorting by priority (after sorting by date) :)))… Now i’m soo inclined to quit advanced queries cause I don’t want every query i want to build to be written by somebody on the forums (most likely you )…

[Siferiax]

@FlorianF

If it was up to me, I would take the “literal” [[Friday, 26.04.2024]], extract the numbers with regex and reverse those ($3$2$1) and use that but I wouldn’t know to do that in closure…

That’s possible, in a way. I mean regex is supported

@FlorianF

Edit: if i use List View it shows the correct order, if I use Table View it’s still nok as it orders “literal” by the day of week starting word of the due-date column. Query: if I clicked on the column header to sort it ascending or descending, does it write that somewhere ins the internal db so that i “remmebers” it? Otherwise I can’t understand why List View would work but not Table View …

That’s because if you click on one of the column headers it will add a query sort to the meta data of the block and that overrides whatever the query tries to do itself
Very annoying! So can either make a new block, or delete the sort line from the file.
Example from one of my files:

- query-sort-by:: block
  query-table:: true
  query-sort-desc:: false
  query-properties:: [:block :datum]
  collapsed:: true
  #+BEGIN_QUERY
  {:title [:b "Session List"]
   :inputs [:query-page]
   :query [:find (pull ?b [*])
    :in $ ?page
    :where
     [?p :block/name ?page]
     [?b :block/page ?p]
     [?b :block/properties ?prop]
     [(get ?prop :datum)]
   ]
   :collapsed? true
  }
  #+END_QUERY

@FlorianF

Kudos to you that you can do it but I don’t think i’ll be ever able to learn advanced queries at such high level and many more like me :(.

Haha thanks. Only took me like idk… a year to get there?!
It took me ~3 months to understand basic advanced queries at all. Like how the syntax should be read/works.
So I understand. It is a VERY steep learning curve. I’ve been consistently (near daily) building/working with advanced queries since November 2022…
I will never claim advanced queries are simple, easy or intuitive. BUT! I’m always happy to lend a hand to build another one

[FlorianF]

I see that but it’s not sustainable. Not for the thousands+ users Logseq should lure with intuitive sense-making of the data they input using the tool… I could learn datalog and what not but why? Aren’t no-code/low-code all the rage now? I have so many other things to do I simply don’t have time and don’t consider it something that should be the de-facto-status. So I refuse it for now, trying to improve my other, more transferabele and future-proof needs like workflows, data quality, etc.