org-ql-search: sort headers according to timestamp globally

[timmli]

I have several org-agenda-files and I'm trying to sort the contained appointments/events depending on their timestamp/date (from newest to oldest) with this code:

(org-ql-search
  (org-agenda-files)
  '(tags "event" "appointment")
  :sort '(date))

Unfortunately, appointments below the same header seem to be grouped together, thus the sorting is not global.
How can I achieve a global sorting of these appointments, regardless of their position in an Org file?

[alphapapa]

org-ql-search returns a list of matching entries (i.e. headings), not a list of appointments or events or timestamps. If an entry contains multiple timestamps...

[timmli]

An "appointment" is, in my case, a header with tag :appointment: and usually an active timestamp. If it contains more than one timestamp, it probably should appear more than once in the list ....?

Here is an example for an Org file:

* Top Header 1
** <2024-02-28 Wed> Appointment 1.1                            :appointment:
** <2024-02-29 Thu> Appointment 1.2                            :appointment:

* Top Header 2
** <2024-02-29 Thu> Appointment 2.2                            :appointment:
** <2024-02-28 Wed> Appointment 2.1                            :appointment:

Using the code above generates the following list, which just mirrors the structure of the file:

  <2024-02-28 Wed> Appointment 1.1                                                                                  :appointment:
  <2024-02-29 Thu> Appointment 1.2                                                                                  :appointment:
  <2024-02-29 Thu> Appointment 2.2                                                                                  :appointment:
  <2024-02-28 Wed> Appointment 2.1                                                                                  :appointment:
 (0:00)

Meanwhile, I found out that :sort '(date) actually only takes the DEADLINE or SCHEDULED property into account. So my code obviously cannot work as intented. 🥇

Instead of using org-ql, I now customize the agenda view in the following way, which pretty much does the desired job:

(setq org-agenda-custom-commands
      `(("e" "Events with timestamp"
         ((tags "event+TIMESTAMP>=\"<now>\""
                ((org-agenda-overriding-header "Future events")
                 (org-agenda-sorting-strategy '((tags ts-down)))
                 (org-agenda-prefix-format '((tags . " %i %-12:c %?-12t")))
                 ))
          (tags "event+TIMESTAMP<=\"<now>\""
                ((org-agenda-overriding-header "Past events")
                 (org-agenda-sorting-strategy '((tags ts-down)))
                 (org-agenda-prefix-format '((tags . " %i %-12:c %?-12t")))
                 ))))))

Still it would be interesting to see how this could be achieved with org-ql.

[alphapapa]

Still it would be interesting to see how this could be achieved with org-ql.

You would need to provide a sort function that collects timestamps in an entry and chooses one (e.g. the earliest or latest) to compare with the one chosen from the other entry. You can see some similar code here:

org-ql/org-ql.el

Lines 2325 to 2388 in e41fe90

	(org-ql-defpred (ts ts-active ts-a ts-inactive ts-i)
	(&key from to _on regexp _with-time
	(match-group 0) (limit (org-entry-end-position)))
	"Return non-nil if current entry has a timestamp in given period.
	Without arguments, return non-nil if entry has a timestamp.
	TYPE may be `active' to match active timestamps, `inactive' to
	match inactive ones, or `both' / nil to match both types.
	LIMIT bounds the search for the timestamp REGEXP. It defaults to
	the end of the entry, i.e. the position returned by
	`org-entry-end-position', but for certain searches it should be
	bound to a different positiion (e.g. for planning lines, the end
	of the line after the heading). MATCH-GROUP should be the number
	of REGEXP's group that matches the Org timestamp (i.e. excluding
	any planning prefix); it defaults to 0 (i.e. the whole regexp)."
	;; MAYBE: Define active/inactive ones separately?
	:normalizers
	((`(,(or 'ts-active 'ts-a) . ,rest) `(ts :type active ,@rest))
	(`(,(or 'ts-inactive 'ts-i) . ,rest) `(ts :type inactive ,@rest))
	(`(,predicate-names . ,(and rest (guard (numberp (car rest)))))
	(org-ql--normalize-from-to-on
	`(ts :type ,type :to ,to)))
	(`(,predicate-names . ,rest)
	(org-ql--normalize-from-to-on
	`(ts :type ,type :from ,from :to ,to))))
	:preambles
	((`(,predicate-names . ,rest)
	(list :regexp (pcase (plist-get rest :type)
	((or 'nil 'both) (pcase-exhaustive (org-ql--plist-get* rest :with-time)
	((or 't "t") org-ql-regexp-ts-both-with-time)
	((or 'nil "nil") org-ql-regexp-ts-both-without-time)
	('not-found org-ql-regexp-ts-both)))
	('active (pcase-exhaustive (org-ql--plist-get* rest :with-time)
	((or 't "t") org-ql-regexp-ts-active-with-time)
	((or 'nil "nil") org-ql-regexp-ts-active-without-time)
	('not-found org-ql-regexp-ts-active)))
	('inactive (pcase-exhaustive (org-ql--plist-get* rest :with-time)
	((or 't "t") org-ql-regexp-ts-inactive-with-time)
	((or 'nil "nil") org-ql-regexp-ts-inactive-without-time)
	('not-found org-ql-regexp-ts-inactive))))
	;; Predicate needs testing only when args are present.
	:query (-let (((&keys :from :to :on) rest))
	;; TODO: This used to be (when (or from to on) query), but
	;; that doesn't seem right, so I changed it to this if, and the
	;; tests pass either way. Might deserve a little scrutiny.
	(if (or from to on)
	query
	t)))))
	:body
	(cl-macrolet ((next-timestamp ()
	`(when (re-search-forward regexp limit t)
	(ts-parse-org (match-string match-group))))
	(test-timestamps (pred-form)
	`(cl-loop for next-ts = (next-timestamp)
	while next-ts
	thereis ,pred-form)))
	(save-excursion
	(cond ((not (or from to)) (re-search-forward regexp limit t))
	((and from to) (test-timestamps (ts-in from to next-ts)))
	(from (test-timestamps (ts<= from next-ts)))
	(to (test-timestamps (ts<= next-ts to)))))))

It's intended to provide more sorting functions like that in the future. It's just a matter of finding the time to work on these ideas.

See also some related code here: https://github.com/alphapapa/unpackaged.el#refile-to-datetree-file-using-earliestlatest-timestamp-in-entry