[web-devel] Join support in persistent

[Michael Snoyman]

Hey all,

After a long discussion with Aur Saraf, I think we came up with a good
approach to join support in Persistent. Let's review the goals:

* Allow for non-relational backends, such as Redis (simple key-value stores)
* Allow SQL backends to take advantage of the database's JOIN abilities.
* Not force SQL backends to use JOIN if they'd rather avoid it.
* Keep a simple, straight-forward, type-safe API like we have
everywhere else in Persistent.
* Cover the most common (say, 95%) of use cases out-of-the-box.

So our idea (well, if you don't like it, don't blame Aur...) is to
provide a separate module (Database.Persist.Join) which provides
special functions for the most common join operations. To start with,
I want to handle a two-table one-to-many relationship. For
demonstration purposes, let's consider a blog entry application, with
entities Author and Entry. Each Entry has precisely one Author, and
each Author can have many entries. In Persistent, it looks like:

Author
    name String Asc
    isPublic Bool Eq
Entry
    author AuthorId Eq
    title String
    published UTCTime Desc
    isPublic Bool Eq

In order to get a list of all entries along with their authors, you
can use the newly added[1] selectOneMany function:

    selectOneMany [AuthorIsPublicEq True] [AuthorNameAsc]
[EntryIsPublicEqTrue] [EntryPublishedDesc] EntryAuthorEq

This will return a value of type:

    type AuthorPair = (AuthorId, Author)
    type EntryPair = (EntryId, Entry)
    [(AuthorPair, [EntryPair])]

In addition to Database.Persist.Join, there is also a parallel module
named Database.Persist.Join.Sql, which has an alternative version of
selectOneMany that is powered by a SQL JOIN. It has almost identical
semantics: the only catch comes in when you don't fully specify
ordering. But then again, if you don't specify ordering in the first
place the order of the results is undefined, so it really *is*
identical semantics, just slightly different behavior.

Anyway, it's almost 1 in the morning, so I hope I haven't rambled too
much. The basic idea is this: Persistent 0.5 will provide a nice,
high-level approach to relations. I'll be adding more functions to
these modules as necessary, and I'd appreciate input on what people
would like to see there.

Michael

[1] https://github.com/snoyberg/persistent/commit/d2b52a6a7b7a6af6234315492f24f821a0ea7ce4#diff-2