How to accomplish a (full) outer join?

[alza-bitz]

Hi there,

I am trying to evaluate if Datascript is the right tool for a problem I'm trying to solve.. and I was wondering how I might accomplish a (full) outer join or equivalent.

For Datomic I found the following article, which mentions two approaches (entity navigation or extension function), and I was wondering if either of these would work with Datascript:

https://support.cognitect.com/hc/en-us/articles/215581518-Outer-Joins

For example, regarding the extension function approach, I didn't find any reference to a maybe function in the Datascript api.

Thanks!

[Harleqin]

maybe for the Datomic example is defined here: https://github.com/Datomic/day-of-datomic/blob/bdb8b4c40c0d8d18b1465b6a7f2f2ce4da226991/src/datomic/samples/query.clj

Datascript has a different model of where the schema is kept, but this should be translatable.

[tonsky]

You can always pass your own fn to the query:

(def db (-> (d/empty-db {:friend {:db/type :db.type/ref
                                  :db/cardinality :db.cardinality/many}})
            (d/db-with [{:db/id 1, :name "1", :friend [2 3]}
                        {:db/id 2, :name "2", :friend [3]}
                        {:db/id 3, :name "3"}])))

(d/q '[:find ?e ?f
       :in $ ?fn
       :where [?e :name _]
              [(?fn $ ?e :friend) ?f]]
      db
      (fn [db e a]
        (->> (d/datoms db :eavt e a)
             (mapv :v)))) ; => #{[2 [3]] [1 [2 3]] [3 []]}

There are also missing? and get-else supported in both Datomic and DataScript: https://docs.datomic.com/on-prem/query.html#get-else

I guess it really depends on your task, but what exactly are you trying to accomplish?

[alza-bitz]

I'm basically looking at ways to do a full outer join in the traditional sense..

For example given "widgets":

[{:db/id 1 :widget/name "a"}
 {:db/id 2 :widget/name "b"}
 {:db/id 3 :widget/name "c"}]

and "gadgets":

[{:db/id 1 :gadget/name "x"}
 {:db/id 3 :gadget/name "y"}
 {:db/id 4 :gadget/name "z"}]

I'm trying to investigate if it would be possible to produce a full outer join (say on id for example, but could be a different attribute):

#{[1 "a" "x"]
  [2 "b" nil]
  [3 "c" "y"]
  [4 nil "z"]}

If that makes sense?

[claj]

The thing about datomic and datascript is that you often can create two queries on the exact same dereffed db, then merge the two result sets, either by a third query or some other means.

In this particular example, I would create two result-sets for id:s, make a union of them and then do pull in a query to get the outer join.

Like:

(let
[db @conn 
 res-1 (q '[:find [?e ...] :where ...] db)
 res-2 (q '[:find [?e ...] :where ...] db)
 joined (clojure.set/union res-1 res-2)
 results (pull-many db [:gadget/name :widget/name] joined)]
results)

[tonsky]

This worked for me

(require '[datascript.core :as d])

(def db (-> (d/empty-db)
          (d/db-with [{:db/id 1 :widget/name "a"}
                      {:db/id 2 :widget/name "b"}
                      {:db/id 3 :widget/name "c"}
                      {:db/id 1 :gadget/name "x"}
                      {:db/id 3 :gadget/name "y"}
                      {:db/id 4 :gadget/name "z"}])))

(d/q '[:find ?e ?w ?g
       :where [?e _ _]
              [(get-else $ ?e :widget/name ::no-value) ?w]
              [(get-else $ ?e :gadget/name ::no-value) ?g]]
      db)

Unfortunately get-else doesn’t accept nil as default value, but using any other value instead works.