joins

joins.clj

(def admins #{{:name "owen" :id 1} {:name "paul" :id 2} {:name "bazzz" :id 3}})
(def orgs #{{:name "xively" :id 1} {:name "cosm" :id 2} {:name "patchbay" :id 3}})
(def groups #{{:name "admins" :id 1} {:name "owners" :id 2} {:name "playas" :id 3}})
(def joins #{{:admin-id 1 :org-id 1 :group-id :1}
             {:admin-id 2 :org-id 1 :group-id :1}
             {:admin-id 2 :org-id 2 :group-id :2}
             {:admin-id 3 :org-id 3 :group-id :3}})

(defn join-tables [src-key dst-key dst-table val]
  (let [;; "select distinct dst-key from joins where src-key = val"
        ids (distinct (filter #(= val (src-key %)) joins))]
    ;; "select * from dst-table where id in ids"
    (map #(select-keys % (keys (first dst-table)))
         (clojure.set/join dst-table ids {:id dst-key}))))

(def all-orgs-by-admin (partial join-tables :admin-id :org-id orgs))
(def all-admins-by-org (partial join-tables :org-id :admin-id admins))

(all-orgs-by-admin 2)
;; ({:id 2, :name "cosm"} {:id 1, :name "xively"})

(all-admins-by-org 1)
;; ({:id 1, :name "owen"} {:id 2, :name "paul"})