search

fukamachi / mito

An ORM for Common Lisp with migrations, relationships and PostgreSQL support
287 stars 31 forks source link

LIST column type and automatically handling many-to-many relations #98

Open zen-wq opened 3 years ago

zen-wq commented 3 years ago

The proposal is to add a column type like (list :integer) which will create a separate table to store list items. List items are all eagerly fetched. Also, handle types like (list person) to create many-to-many relations with optional eager loading (with includes).

Do you think it's a good fit for mito or is it too far from plain SQL?

zen-wq commented 3 years ago

It can be easily if a little kludgy implemented as an inflator/deflator. A list of objects that is deflated with (prin1-to-string (mapcar #'mito:object-id list)) and inflated with (mito:select-dao 'class (where (:in :id (read-from-string data))))

Is it worth it to implement many-to-many relations properly with a :col-type and an automatic intersection table?

fukamachi commented 3 years ago

I've been thinking of how we can make table relations easy in Mito. So, this is actually worth discussing.

We can use many-to-many relational tables for joining with both tables, but your list type can't. There's no effective way to fetch rows related to a person row in your example.

Due to its inflexibility, I'm not sure that there are many situations where we can use it.

zen-wq commented 3 years ago

My second comment was a simple kludgy way of implementing it without touching mito. The original proposal (the first comment) was to automatically create and maintain many-to-many relational tables in mito.

eko234 commented 3 years ago

I think maybe extending include macro could work, let's imagine we have a human tablet, an eye-color table and a human-eye-color table, now let's supose include macro had the following syntax

(mito:select-dao 'eye-color (includes 'human :from 'human-eye-color))

Keep heterochromia in mind.

Or maybe a has macro with a syntax like: (mito:select-dao 'human (has 'eye-color from 'human-eye-color))

I think the later could solve needs that some users are trying to work around with inflate functions, but I think buffing inflation functions would give users more power also.

Being able to chain relations would be very nice, like in the last examen example.