This page is not created by, affiliated with, or supported by Slack Technologies, Inc.
I am trying to write a query, and am still learning xtdb so seeking some help.
I have two kinds of entities: post and commentary. As the names suggest, commentary entity belongs to one of post entity. In other words, a post may relate to multiple commentaries.
So the maps would look like
{:post/id ...
:post/last-edited-at ...}
{:commentary/id ...
:commentary/post-id ...
:commentary/last-edited-at ...}
posts by the order of last-edited-at of both post itself and the latest ones of its related commentaries. How can I do that?I think this is similar to how we would solve in sql like https://stackoverflow.com/questions/20028093/mysql-order-posts-by-most-recent-comment-or-last-posted
You can do so with a subquery + using or-join to replace COALESCE, I've broken down how I got to it into steps so it's (hopefully) easier to follow:
(xt/submit-tx xtdb-node
[[::xt/put {:xt/id :post-1 :post/last-edited-at #inst "2020"}]
[::xt/put {:xt/id :post-2 :post/last-edited-at #inst "2021"}]
[::xt/put {:xt/id :post-3 :post/last-edited-at #inst "2022"}]
[::xt/put {:xt/id :comment-1 :post/id :post-1 :commentary/last-edited-at #inst "2020-01"}]
[::xt/put {:xt/id :comment-2 :post/id :post-1 :commentary/last-edited-at #inst "2020-02"}]
[::xt/put {:xt/id :comment-3 :post/id :post-1 :commentary/last-edited-at #inst "2023"}]
[::xt/put {:xt/id :comment-4 :post/id :post-2 :commentary/last-edited-at #inst "2021-01"}]
[::xt/put {:xt/id :comment-5 :post/id :post-2 :commentary/last-edited-at #inst "2021-02"}]
[::xt/put {:xt/id :comment-6 :post/id :post-3 :commentary/last-edited-at #inst "2024"}]])
; Posts in order of `post/last-edited-at`
(xt/q (xt/db xtdb-node)
'{:find [post-id last-edited-at]
:where [[post-id :post/last-edited-at last-edited-at]]
:order-by [[last-edited-at :desc]]})
; => [[:post-3 #inst "2022-01-01T00:00:00.000-00:00"]
; [:post-2 #inst "2021-01-01T00:00:00.000-00:00"]
; [:post-1 #inst "2020-01-01T00:00:00.000-00:00"]]
; Comments ordered by `commentary/last-edited-at`
(xt/q (xt/db xtdb-node)
'{:find [post-id commentry-id commentry-edit]
:where [[commentry-id :post/id post-id]
[commentry-id :commentary/last-edited-at commentry-edit]]
:order-by [[commentry-edit :desc]]})
; => [[:post-1 :comment-3 #inst "2023-01-01T00:00:00.000-00:00"]
; [:post-3 :comment-6 #inst "2022-01-01T00:00:00.000-00:00"]
; [:post-2 :comment-5 #inst "2021-02-01T00:00:00.000-00:00"]
; [:post-2 :comment-4 #inst "2021-01-01T00:00:00.000-00:00"]
; [:post-1 :comment-2 #inst "2020-02-01T00:00:00.000-00:00"]
; [:post-1 :comment-1 #inst "2020-01-01T00:00:00.000-00:00"])
; Posts and their (ordered) comment-edits
(xt/q (xt/db xtdb-node)
'{:find [post-id post-edit comments]
:where [[post-id :post/last-edited-at post-edit]
[(q {:find [commentry-edit]
:where [[commentry-id :post/id post-id]
[commentry-id :commentary/last-edited-at commentry-edit]]
:in [post-id]
:order-by [[commentry-edit :desc]]}
post-id)
comments]]
:order-by [[post-edit :desc]]})
; => [[:post-3
; #inst "2022-01-01T00:00:00.000-00:00"
; ([#inst "2022-01-01T00:00:00.000-00:00"])
; [:post-2
; #inst "2021-01-01T00:00:00.000-00:00"
; ([#inst "2021-02-01T00:00:00.000-00:00"]
; [#inst "2021-01-01T00:00:00.000-00:00"])]
; [:post-1
; #inst "2020-01-01T00:00:00.000-00:00"
; ([#inst "2023-01-01T00:00:00.000-00:00"]
; [#inst "2020-02-01T00:00:00.000-00:00"]
; [#inst "2020-01-01T00:00:00.000-00:00"])])
; Posts and their latest comment-edit
(xt/q (xt/db xtdb-node)
'{:find [post-id (or post-edit latest-comment-edit)]
:where [[post-id :post/last-edited-at post-edit]
[(q {:find [commentry-edit]
:where [[commentry :post/id post-id]
[commentry :commentary/last-edited-at commentry-edit]]
:in [post-id]
:order-by [[commentry-edit :desc]]
:limit 1}
post-id)
comments]
[(ffirst comments) latest-comment-edit]
[(or comments) latest-comment-edit]]
:order-by [[latest-comment-edit :desc]]})
; => [[:post-1
; #inst "2020-01-01T00:00:00.000-00:00"
; #inst "2023-01-01T00:00:00.000-00:00"
; [:post-3
; #inst "2022-01-01T00:00:00.000-00:00"
; #inst "2022-01-01T00:00:00.000-00:00"]
; [:post-2
; #inst "2021-01-01T00:00:00.000-00:00"
; #inst "2021-02-01T00:00:00.000-00:00"]]
; We need this because `max` can't compare dates
(defn max-date [a b]
(if (>= (compare a b) 0)
a b))
(assert (= (max-date #inst "2020" #inst "2021")
(max-date #inst "2021" #inst "2020")))
; Posts sorted by either their latest comment-edit,
; or their edit date
(xt/q (xt/db xtdb-node)
'{:find [post-id sort-date]
:where [[post-id :post/last-edited-at post-edit]
[(q {:find [commentry-edit]
:where [[commentry :post/id post-id]
[commentry :commentary/last-edited-at commentry-edit]]
:in [post-id]
:order-by [[commentry-edit :desc]]
:limit 1}
post-id)
comments]
[(ffirst comments) latest-comment-edit]
(or-join [; inputs
post-edit latest-comment-edit
; output
sort-date]
[(scratch/max-date post-edit latest-comment-edit)
sort-date]
; The `and nil?` is because `or-join` will
; choose *all* matching branches
(and [(nil? latest-comment-edit)]
[(identity post-edit) sort-date]))]
:order-by [[sort-date :desc]]})
; => [[:post-3 #inst "2024-01-01T00:00:00.000-00:00"]
; [:post-1 #inst "2023-01-01T00:00:00.000-00:00"]
; [:post-2 #inst "2021-02-01T00:00:00.000-00:00"]]