So, I'm building a book database, and using it as a learning exercise
for UncommonSQL. I'm using the object-oriented interface, which makes
some things far nicer than they would be without UncommonSQL, but I've
encountered a difficulty.
The relationship between books and authors is many-to-many, and I'm
modelling it in the obvious (and, I think, standard) way: as well as
a table (in UncommonSQL-speak, a view class) for books and another for
authors, I have a third for "authorships", each row of which records
the fact that such-and-such an author contributed to such-and-such a
book.
So, I have these three book classes. BOOK and AUTHOR each have
slots called ID serving as primary keys; AUTHORSHIP has a slot
called BOOK-ID and another called AUTHOR-ID.
Given a book, I want to be able to retrieve a list of its authors.
It's easy to get a list of the IDs of its authors:
(sql:def-view-class authorship ()
((book-id :type integer :blah-blah blah)
(author-id :type integer :blah-blah blah)))
(sql:def-view-class author ()
((id :type integer :blah-blah blah)
...))
(sql:def-view-class book ()
((id :type integer :blah-blah blah)
...
(author-ids :db-kind :join
:db-info (:join-class authorship
:home-key id
:foreign-key book-id
:target-slot author-id
:set t))))
or to retrieve instead the authorship objects and then go
from them to the authors:
(sql:def-view-class authorship ()
((book-id :type integer :blah-blah blah)
(author-id :type integer :blah-blah blah)
...
(author :db-kind :join
:db-info (:join-class author
:home-key author-id
:foreign-key id
:set nil))))
(sql:def-view-class author ()
((id :type integer :blah-blah blah)
...))
(sql:def-view-class book ()
((id :type integer :blah-blah blah)
...
(authorships :db-kind :join
:db-info (:join-class authorship
:home-key id
:foreign-key book-id
:set t))))
but either of these is going to require multiple queries
if I want all the authors of a book, when in fact a single
query can do it (via a three-way join on the AUTHOR, BOOK
and AUTHORSHIP tables).
The UncommonSQL tutorial says, near the start, "later we
will explain how to model many-to-many relations", but it
doesn't appear to make good on that promise; perhaps the
intention once upon a time was that when it explained how
to model many-to-may relations it would also explain how
to query them efficiently.
This isn't insoluble; obviously it's possible to construct
a SQL query explicitly that does this, something along these
lines:
(sql:select 'authorship 'author
:where [and [= [slot-value 'authorship 'author-id]
[slot-value 'author 'id]]
[= [slot-value 'authorship 'book-id]
<the ID of the book in question>]])
But is there a way to get something like this effect more
concisely using magical slots in the view classes?
--
Gareth McCaughan
.sig under construc