Advanced query that shows TODO with property and sorts output

[atomicangel]

Hello everyone, new LogSeq user here. I have some experience writing code, so this isn’t too far off from what I know. That said, I’m having some difficulty understanding how to do a query based on a property simply existing and then displaying the results in a list that is sorted by date and then the property value.

Here is a simple query that does what I want, but doesn’t work in table format due to the actual TODO item not being displayed in the table: (It does display when in list view.)

{{query (and (between <% today %> +5d) (property :time) (sort-by))}}

Screenshot:

Here is an advanced query that partially gets me there, but it isn’t showing the time property at all and also isn’t filtering anything prior to today, so the filter isn’t quite working.

#+BEGIN_QUERY
{ :title "Tomorrow Advanced"
: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 
      [(= "TODO" ?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

I’m not entirely sure how to both check that the property simply exists (the values will always be some kind of 24-hour time, but as a string since I don’t see a time formatting tool besides /Current-Time) and then while showing that + the TODO block and page it’s on sort out the TODOS by the time listed as show in the simple query screenshot.

The goal is to get what I have in the 1st screenshot, but into a table format if possible. If not, I’ll settle for the list as that is better than nothing at all. Any advice on what I can do without using a plugin? (I’m wanting this to be compatible with my phone as well as my laptop.)

[atomicangel]

Follow-up to add that I found this LogSeq advanced query building tool and it’s useful in helping learn how the code works, but it hasn’t been able to solve my problem. I think this is partially due to me not knowing how to phrase my query entirely, and also possibly due to the setup of my TODO’s. It appears I can’t add a custom property to the TODO line itself, thus relegating it to a child line of the actual TODO. Since it is late here I will try again in the morning but my thoughts are if I can get it to check descendants that will help me find the TODO with a time stamp, and go from there.

Still have no idea how to handle the output formatting, and the documentation doesn’t explain it in a way I’m able to understand at the moment.

[Siferiax]

The problem is actually in the data.
The TODO and the time property are not in the same block.
You have

- Todo
   - time:: value

Instead of

- TODO
time:: value

This way your simple query should work.

For the advanced query, not sure exactly about that syntax lol. I feel it can be more simple.
I think the simple query should be enough though with this data adjustment.

[atomicangel]

Ok so this simple query works now that you have clarified my error.

{{query (and (task TODO) (property :time) (between <% tomorrow %> +5d))}}

It now pulls in the property as expected and shows it in the list. That said, I couldn’t get the sort-by page asc to work so I went into the file outside of LogSeq and changed the value for query-sort-desc:: to true and made sure the value query-sort-by:: was set to page. That sorted out the list the way I wanted it, so it’s now working as intended. Not sure if that will stick when copying and making it a template, or if it will stick once it’s a template even but either way I know what to do about that going forward.

I’ll mark your post as the solution

[Siferiax]

2 things.

1. The documentation states that the sort by only works for properties. You will need an advanced query otherwise.
2. Those in file properties are changable in app when you make your query output in table view. Just click the column header.