How to accomplish a (full) outer join? #278

Activity

1. Harleqin commented on Nov 26, 2018

   

   Harleqin

   on Nov 26, 2018

   More actions

   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.
2. tonsky commented on Nov 26, 2018

   

   tonsky

   on Nov 26, 2018

   Owner

   More actions

   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?
3. alza-bitz commented on Nov 30, 2018

   

   alza-bitz

   on Nov 30, 2018

   Last edited by alza-bitz

   Author

   More actions

   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?
4. claj commented on Dec 3, 2018

   

   claj

   on Dec 3, 2018

   Last edited by claj

   Contributor

   More actions

   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)
   
   

   👍React with 👍2Reacted by Miguel SM and karlicoss
5. tonsky commented on Dec 6, 2018

   

   tonsky

   on Dec 6, 2018

   Owner

   More actions

   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.