Retreiving all tweets using *user* via relationship over :col-type

[aykaramba]

I have been exploring how mito handles relationships between tables because Mito doesn’t add foreign key constraints for refering tables. This is fine, it is done at the CLOS level and makes sense once you wrap your head around it. However, using RELATIONSHIP examples here and here:

Link 1: https://github.com/fukamachi/mito
Link2: https://lisp-journey.gitlab.io/blog/models-and-databases-mito-sxql/

... after we create the user and tweet tables, insert a few tweet entries using the user variable, we can only ever retrieve the first tweet using the following sample code: (mito:find-dao 'tweet :user user)

To replicate we are using the example from Link2 above:

1. First we crate a user table:

(defclass user ()
                   ((name :col-type (:varchar 64)
                                   :initarg :name
                                   :accessor user-name)
                     (email :col-type (:varchar 128)
                                 :initarg :email
                                 :accessor user-email))
                    (:metaclass mito:dao-table-class)
                    (:unique-keys email))

2. Then we create a tweet table:

(defclass tweet ()
                   ((status :col-type :text
                                 :initarg :status
                                 :accessor tweet-status)
 
                      ;; This slot refers to USER class
                      (user :col-type user
                                :initarg :user
                                :accessor tweet-user))
                    (:metaclass mito:dao-table-class))

3. We populate the user var:

(defvar *user* (mito:create-dao 'user :name "Eitaro Fukamachi" :email "aaa@aaa.aaa"))

4. We insert a bunch of tweets for user:

(mito:create-dao 'tweet :user *user* :status "hello there.")
(mito:create-dao 'tweet :user *user* :status "hello there.")
(mito:create-dao 'tweet :user *user* :status "hello there.")
(mito:create-dao 'tweet :user *user* :status "hello there.")

Note: We now have 4 tweets in the tweet table, user_id is 1 and status is "hello there.". We should now be able to query the db and list all the tweets using different approaches.

5. We try to list all tweets by user:

(mito:find-dao 'tweet :user *user*)

And we only get one result instead of 4:

#<TWEET {1004C17233}>

6. Obviously we can easily list all tweets like this:

(mito:select-dao 'tweet) 
(#<TWEET {1004CD9DE3}> #<TWEET {1004CD9FD3}> #<TWEET {1004CDA1C3}> #<TWEET {1004CDA3B3}>}

We can also do something like this using sxql:

(mito:select-dao 'tweet  (sxql:where (:= :user-id 1)))
(#<TWEET {1004CD9DE3}> #<TWEET {1004CD9FD3}> #<TWEET {1004CDA1C3}> #<TWEET {1004CDA3B3}>}

QUESTION: Why does (mito:find-dao 'tweet :user user) return only the first tweet? I expected that to return all tweets by user, what is going on here and what might be the canonical way to just user the CLOS object bound to user to look up all tweets by that user as that seems nicer than the sxql approach?

[vindarel]

I think the sxql :where query is the canonical way. If find-dao is supposed to return 1 and only 1 result, then it's consistent.

I agree that Mito would benefit from some higher-level query functions (such as find-all or count with a faster way to filter by columns), but this might be of our responsibility, the community, to create. Or, we decide those functions would be too tied to a particular project and setup, and we accept to write those for each project.

my 2c. Thanks for posting, I appreciate reading other people's approach on Mito.

[aykaramba]

Oh I see what you are saying. Yes agreed on all points. I am getting to the point where perhaps I can fiddle with adding something like find-all or count as described above, it would make a good tutorial for lisp as well.

Thanks for the follow up. I love white boarding with others, it helps me get out of the echo chamber of my own mind.

Also, your tutorials are TRULY spot on. They are just technical enough to help with the "thinking through an issue". Your work is really appreciated, as the prodigous contributions of Fukumachi, to whom I am ever grateful for the amazing Mito library.