That is wonderful- application joins or database joins. Lets compare to Rails:
selectOneMany [AuthorIsPublicEq True] [AuthorNameAsc] [EntryIsPublicEqTrue] [EntryPublishedDesc] EntryAuthorEq
Author.where(:isPublic => true).order("name").includes(:entries) & Entry.where(:isPublic => true).order("published DESC")
Note that a Rails query is lazy and the & is combining the queries. However, when there are no filtering criteria on the association, Rails prefers to perform 2 queries- one to retrieve the authors, and then one to retrieve the entries based on the author ids:
SELECT "entries".* FROM "entries" WHERE ("entries".author_id IN (51,1,78,56,64,84,63,60))
Originally rails always did do a SQL level join, but then decided to switch to preferring app-level, largely because it reduced the number of Ruby objects that needed to be allocated, resulting in much better performance for large data sets [1].
It appears that persistent.Join is instead performing an n + 1 query- one query per each author. We should avoid these kinds of queries, and then there will not be much point to an outer join in the db.
Looking at the behavior of Rails for joins, I don't like how it decides between types of joins. The sql produced by Rails is not in fact identical to the persistent one: it will do an outer join with the entry filtering in a WHERE clause, not as part of the JOIN conditions.
If we are to support joins it needs to be very apparent which type of join is performed.
selectOneMany doesn't have an offset and limit. If we added it we end up with queries like this:
selectOneMany [] [] [] [] EntryAuthorEq 0 0
This function with 5+ required arguments is somewhat awkward/difficult to use and to read. Rails is composable in a readable way because it copied haskellDB. I would like to get away from the empty optional arguments.
I am all for adding these changes for now, I just hope we can move to a more composable API in the future.
I thought the API that Aur came up with was a better effort in that direction, although there are definitely practical issues with it.
Greg Weber
On Sat, Apr 2, 2011 at 2:50 PM, Michael Snoyman
<michael@snoyman.com> wrote:
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
_______________________________________________
web-devel mailing list
web-devel@haskell.org
http://www.haskell.org/mailman/listinfo/web-devel