store a list of daos in a slot

[hermanhel]

The following should allow usage of :daos in :col-type which accepts a list of dao that have already been mito:save-dao-ed to the same db.

(defun mito-id (obj)
  (slot-value obj 'mito.dao.mixin::id))

(defun daos-to-table-ids (daos)
  (if daos
      (format nil "~A" (mapcar (lambda (dao) (list (type-of dao) (mito-id dao))) daos))
      "()"))

(defun table-ids-to-daos (str)
  (mapcar (lambda (elt)
            (let ((class (first elt))
                  (id (second elt)))
              (mito:find-dao class :id id)))
          (read-from-string (or str "()")))

(defmethod mito.dao.column:deflate-for-col-type ((col-type (eql :daos)) value)
  (pat2::daos-to-table-ids value))

(defmethod mito.dao.column:inflate-for-col-type ((col-type (eql :daos)) value)
    (pat2::table-ids-to-daos value))

For example

(deftable foo ()
  ())
(deftable bar ()
  ((foos :accessor foos
         :initform nil
         :col-type (or :null :daos))))

(let ((foo1 (make-instance 'foo))
      (bar1 (make-instance 'bar)))
  (connect-toplevel :sqlite3 :database-name ":memory:")
  (ensure-table-exists 'foo)
  (ensure-table-exists 'bar)
  (push foo1 (foos bar1))
  (save-dao foo1)
  (save-dao bar1)
  (foos (car (select-dao 'bar))))
;; => (#<FOO {100ABBF303}>)

couple of gotchas:

• type-of do not return symbol with package name, so any class you use in the :daos slot need to be imported and available in the current package, otherwise find-dao will complain.
• in table schema and create-table the slot's column will be of type daos. This is fine in sqlite3 and the column is stored as text (or blob, not sure). May pose problem in mysql/postgresql. I imagine this could be solved with an :around on mito.class.column:table-column-info, or redefinition of the main adding a cond clause for each driver-type, maping :col-type :dao to :varchar or something.
• the objects in :daos slot need to be saved before the object with :daos slot is saved. with the example above, foo objects in (foos bar1) needs to be save-dao-ed before bar1 is save-daoed, as the foo object is refered to with its id, which is filled in save-dao.

One more thing is that similar custom col-type can be made easily with defmethod on the above 2 methods. I imagine it would be useful for repeated slot definition with the same set of :inflate and :deflate, which is what I did before this.

2 other examples are :ignored col-type with both methods defined to return nil, hence its column in table is always null, and retrived dao nil. I used that on slots with mostly functions that I reset with other functions in code; and :princ col-type that uses princ-to-string and read-from-string for deflate and inflate, used on list of printable objects, as currently list in slot will result in error in sxql.

[daninus14]

This is against the workflow of how normalized relational databases work. You should learn a bit of databases.

When you have a 1-N relationship, you store the relationship on the N side. You can make a ghost slot on the 1 side, and then make a DB request to bind the data to that slot.

This is application dependent, not something that should be generalized. Even in the same application, and the same model, sometimes you may not want to load that data to begin with.

Please close the issue.

[fukamachi]

Thank you for your suggestion.

It looks quite natural to do it in "the CLOS way", but as @daninus14 said, it's not how RDB works.
I've been struggling to provide a clear & efficient syntax for relationships, but I haven't got a solution yet.

Although it's not exactly what you want, define-accessor may help define a read accessor to get related DAOs from its parent side.
https://github.com/fukamachi/mito?tab=readme-ov-file#defining-one-to-many-and-many-to-many-relationship-accessors

It internally uses mito:dao-cache for storing results in objects, which you might be able to use for your extension.