join queries with mito

[rajasegar]

I have a table schemas like the below:

(mito:deftable warehouses ()
  ((location :col-type (:varchar 50))
   (capacity :col-type (:integer))))

(mito:deftable boxes ()
  ((contents :col-type (:varchar 10))
   (value :col-type (:integer))
   (warehouse :col-type warehouses :references warehouses)))

How can I fetch the list of boxes along with their warehouse names, it is pretty straigthforward with sql or sxql, but I want to know how can we do this with mito?

select contents, value, location from boxes inner join warehouses where boxes.warehouse = warehouse.id

[vindarel]

---

test code:

(mito:connect-toplevel :sqlite3 :database-name "mito-example.db")

(mapc #'mito:execute-sql (mapcan #'mito:table-definition '(WAREHOUSES boxes)))

;; data:
(mito:create-dao 'boxes :contents "foo" :value 9 :warehouse (mito:create-dao 'warehouses :location "here" :capacity 2))
;; #<BOXES {10060E3353}>

---

(inspect *)

The object is a STANDARD-OBJECT of type BOXES.
0. CREATED-AT: @2021-07-20T11:57:21.775323+02:00
1. UPDATED-AT: @2021-07-20T11:57:21.775323+02:00
2. SYNCED: T
3. ID: 1
4. CONTENTS: "foo"
5. VALUE: 9
6. WAREHOUSE: #<WAREHOUSES {1005942393}>
7. WAREHOUSE-ID: "unbound"

Normally there is built-in accessor to warehouse but it is broken (https://github.com/fukamachi/mito/issues/79)

You can add an accessor yourself.

…
   (warehouse :col-type warehouses :references warehouses
              :accessor warehouse)))  ;; <--- added
…

(warehouse **)
;; #<WAREHOUSES {1005942393}>

Was that your goal or it was to only get a warehouse.location data?

[rajasegar]

@vindarel Thanks, and yes that was my goal, I kinda figured it out later to use includes

(mito:select-dao 'boxes (mito:includes 'warehouses))

This works for my use case