Finding 2 duplicate k-v pairs in a sort of a junction table with XTDB

[mjholub]

Hello and thank you for this amazing framework.

I'm struggling to create a query to properly find a pair of duplicate values for :saved-posts/user and :saved-posts/post. Here, result is an empty set even when it shouldn't

(defn check-duplicate-bookmark
  [{:keys [biff/db user-id post-id] :as ctx}]
  (let [result (biff/q db '{:find [saved-post]
                            :in [user post]
                            :where [[saved-post :saved-posts/user user]
                                    [saved-post :saved-posts/post post]]}
                       user-id post-id)]
;; ...

should not be empty. But according to my logs and tests, posts are getting saved twice by this function:

(defn bookmark-post [{:keys [biff/db user-id post-id] :as ctx}]
  (with-error-handling (format "bookmarking post %s for user %s" post-id user-id) {:trace false}
    ;; do an early return
    (check-duplicate-bookmark ctx)
    (let [bid (random-uuid)
          now (Date.)]
      (biff/submit-tx ctx [{:db/doc-type :saved-posts
                            :xt/id bid
                            :saved-posts/user (:user-id ctx)
                            :saved-posts/post (:post-id ctx)
                            :saved-posts/saved-at now}])
      (log/infof "User %s saved post %s" user-id post-id)
      {:bookmark-id bid :saved-at now})))

extract from my schema (see here for complete schema if needed):

   :user (doc {:id :user/id
               :required [:user/url
                          :user/inbox
                          :user/outbox
                          :user/password
                          :user/joined-at
                          :user/ip]
               :optional [:user/hidden-posts
                          :user/muted-instances
                          :user/hidden-keywords]})

   :post (doc {:id :post/id
               :required [:post/user
                          :post/message
                          :post/num
                          :post/created-at
                          :post/ap-id
                          :post/thread-root]
               :optional [:post/attachments
                          :post/in-reply-to
                          :post/quote-of
                          :post/topic
                          :post/mentions
                          :post/updated-at
                          :post/sage
                          :post/visibility
                          :post/cw]})

   :saved-posts [:map {:closed true}
                 [:xt/id :uuid]
                 [:saved-posts/post :post/id]
                 [:saved-posts/user :user/id]
                 [:saved-posts/saved-at inst?]]

Basically I need to do a query roughly equivalent to SELECT sp.id FROM saved_posts sp WHERE sp.user_id = ? AND sp.post_id = ?

Unless the issue is with how I'm inserting my test data, but it does not seem to be the case since the query succeeds?

[jacobobryant]

Hey!

Nothing jumps out at me as being incorrect. Have you tried querying from the repl? e.g. in repl.clj does the following return some results?

(let [{:keys [biff/db] :as ctx} (get-context)]
    (q db
       '{:find (pull doc [*])
         :where [[doc :saved-posts/user]]}))

From there you should at least be able to confirm if the problem is with inserting the data or querying the data (or handling the data after it's queried).

By the way, should :saved-posts/post and :saved-posts/user together be unique, i.e. are they supposed to be a composite key? if so, you could do an upsert operation:

(biff/submit-tx ctx [{:db/doc-type :saved-posts
                      :db.op/upsert {:saved-posts/user (:user-id ctx)
                                     :saved-posts/post (:post-id ctx)}
                      :saved-posts/saved-at [:db/default now]}])

That might save you from needing to do some of this logic on your own in the first place.

[mjholub]

well I've eventually decided to move my appllication to sqlite since I just want a self-contained db that I know pretty well and that has a small resource footprint. Thanks for your suggestions, will reopen that if I change my mind and still fail to resolve this (or if I do, just for the sake of posteriority).

[mjholub]

yeah, upsert did the trick

[jacobobryant]

Sounds good!

[mjholub]

also I needed to use-fixtures in my test to ensure the bookmark is actually saved before checking in the relevant collection and that the same user-id is referenced in both places.