Todo list query with multiple conditions - LEFT JOIN

Questions & Help
1

Hello,
I am trying to build the following logic to capture my running task of the day.
I can make it as separate queries, but it’d way easier to join it into a single one.

Idea is to get:
(anything with #tod tag AND not done)
OR (anything that is scheduled for today or prior AND not done)
OR (anything that is having DOING status)

Extrapolating the query example I am building on, it seems that the more I add analysis paths (ie linking to marker, name, schedule info) the more it assumes them as mandatory in my dataset and limiting the output. A bit like similar to using INNER JOINs in SQL.

Any idea how to create multiples analysis path more like LEFT JOIN ? Ie, is there is not scheduled info, still keep the block.

Thanks

#+BEGIN_QUERY
{ :query [:find (pull ?b [*])
  :in $ ?today ?tomorrow
  :where
    [(get-else $ ?b :block/marker "null") ?m]
    [(get-else $ ?b :block/name "null") ?tag]
    [(get-else $ ?b :block/scheduled 0) ?d]
    (or 
      (or [(= "tod" ?tag)] [(= "null" ?tag)])
      (or [(= "DOING" ?m)] [(= "null" ?m)])
      (or [(<= ?d ?today)] [(= 0 ?d)])
    )
]
:inputs [:today :1d-after]
:breadcrumb-show? false}
#+END_QUERY
2

I inverted the query to have the OR statement first. It seems that it is not restricting data set as they are being applied.

I then subsequently apply filters that do restrict the dataset with get-else logic not to remove rows if they don’t have the matching tag.

#+BEGIN_QUERY
{:title "All for Today (tod)/A/DOING/Scheduled"
 :query [:find (pull ?b [*])
		:in $ ?today ?tomorrow
       :where
		
				(or 
					[?b :block/ref-pages [:block/name "tod"]]
					[?b :block/priority "A"] 
					[?b :block/marker "DOING"]
					[?b :block/scheduled]
					
				)
                               [(get-else $ ?b :block/priority"NIL") ?prio]

				[(get-else $ ?b :block/marker "NIL") ?marker]
				[(not= ?marker "DONE")]
				
				[(get-else $ ?b :block/scheduled ?today) ?d]
				[(<= ?d ?today)]
	
	
		]
		:inputs [:today :1d-after]
		:breadcrumb-show? false
		:result-transform (fn [result]
			(sort-by (fn [h]
			(get h :block/priority "Z")
			)
			 result))
		  :collapsed? false}
	   }
#+END_QUERY
3

Hello, would you help me out to tell how you write the query “anything that is scheduled for today or prior AND not done” (as an individual, separate query like you’ve mentioned in your post).

Many Thanks!

4

@Simeonh2021 this is a simplified version of my original attempt.
For (SCHEDULED TODAY||PRIOR) && (NOT DONE, NOT CANCELED, NOT LATER) it would be something like below.

Logic is

  1. Get all scheduled => [?b :block/scheduled]
  2. See if they have a status, if so filter over it => [(get-else $ ?b :block/marker “NIL”) ?marker] …
  3. Filter by target date => [(get-else $ ?b :block/scheduled ?today) ?d][(<= ?d ?today)]

Query

#+BEGIN_QUERY
{:title "☀️ TOD V2"
 :query [:find (pull ?b [*])
		:in $ ?today ?tomorrow
       :where
		
				[?b :block/scheduled]

                                [(get-else $ ?b :block/priority"NIL") ?prio]

				[(get-else $ ?b :block/marker "NIL") ?marker]
				[(not= ?marker "DONE")]
				[(not= ?marker "CANCELED")]
                       		[(not= ?marker "LATER")]
				
				[(get-else $ ?b :block/scheduled ?today) ?d]
				[(<= ?d ?today)]
	
	
		]
		:inputs [:today :1d-after]
		:breadcrumb-show? false
		:result-transform (fn [result]
			(sort-by (fn [h]
			(get h :block/priority "Z")
			)
			 result))
		  :collapsed? false
}
#+END_QUERY
5

Thank you so much! (especially for your kind explanation!) I will test it.

1 Like