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

My tasks have a property called due-by:: which can contain one or more dates in Logseq format, ex:
due-by:: [[Monday, 22.04.2024]] [[Thursday, 23.05.2024]]
and I want to make a query like described in the title.
I wanted to use the simple queries inside complex ueries tags, like so:

+BEGIN_QUERY
{
:title [:h3 "Soon... (7-30 days):"]
:query (and (property status "#tocall ") (property due-by (between "+7d" "+30d"))  (sort-by due-by desc) )
:breadcrumb-show? false
}
#+END_QUERY

Is it so that the above is impossible to do (I mean with Simple Queries in Advanced Query dressing :slight_smile: )?

No but we need an advanced query for it.
Step 1: get the journal page references from the property
Step 2: do the regular comparison for journal dates in advanced queries.

I’m on my way to bed now, I’ll come back with something workable tomorrow if someone didn’t beat me to it :slight_smile:

I tried to wrap my head around Advanced Queries but it’s a no-no atm :frowning:
The solution I use so far is a low-level simple query that involves
(or (property due-by <%in 8 days%>) (property due-by <%in 9 days%>) etc)
, which was quite a task to do for 30 days :)))

That’s ok :slight_smile:
Here’s a query, I hope this is what you need. Let me know if adjustments are necessary!

#+BEGIN_QUERY
{:title [:h3 "Soon... (7-30 days):"]
 :inputs [:+7d :+30d]
 :query [:find (pull ?b [*])
  :in $ ?7d ?30d
  :where
   [?b :block/properties ?prop]
   [(get ?prop :due-by) ?due] ;get the 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
   [(<= ?7d ?d ?30d)] ;check that the journal page is in range
 ]
 :breadcrumb-show? false
}
#+END_QUERY

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 :see_no_evil:;
  • 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

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”. :+1:t4:

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.

1 Like

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

#+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 :slight_smile: 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 :slight_smile:

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.

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

ok, got it. Corrected above.

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…

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)?

I honestly don’t know how :slight_smile:
With regex maybe?

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

...
[(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 :slight_smile:

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]]”).

image

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”… :confused:

image

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)

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:

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… :see_no_evil:

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?