<div>That is wonderful- application joins or database joins. Lets compare to Rails:</div><div><br></div><div><div>selectOneMany [AuthorIsPublicEq True] [AuthorNameAsc] [EntryIsPublicEqTrue] [EntryPublishedDesc] EntryAuthorEq</div>
</div><div><br></div>Author.where(:isPublic => true).order("name").includes(:entries) & Entry.where(:isPublic => true).order("published DESC")<div><br></div><div>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:</div>
<div><div><br></div><div>SELECT "entries".* FROM "entries" WHERE ("entries".author_id IN (51,1,78,56,64,84,63,60))</div></div><div><br></div><div>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].</div>
<div><br></div><div>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.</div>
<div><br></div><div>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.</div>
<div>If we are to support joins it needs to be very apparent which type of join is performed.</div><div><br></div><div><br></div><div>selectOneMany doesn't have an offset and limit. If we added it we end up with queries like this:</div>
<div>
<div><div><br></div><div>selectOneMany [] [] [] [] EntryAuthorEq 0 0</div><div><br></div><div><div>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.</div>
</div><div>I am all for adding these changes for now, I just hope we can move to a more composable API in the future.</div>
</div><div>I thought the API that Aur came up with was a better effort in that direction, although there are definitely practical issues with it.<br><div><br></div><div>[1] <a href="http://akitaonrails.com/2008/05/25/rolling-with-rails-2-1-the-first-full-tutorial-part-2" target="_blank">http://akitaonrails.com/2008/05/25/rolling-with-rails-2-1-the-first-full-tutorial-part-2</a></div>
<div><br></div><div>Greg Weber</div><div><br><div class="gmail_quote">On Sat, Apr 2, 2011 at 2:50 PM, Michael Snoyman <span dir="ltr"><<a href="mailto:michael@snoyman.com" target="_blank">michael@snoyman.com</a>></span> wrote:<br>
<blockquote class="gmail_quote" style="margin:0 0 0 .8ex;border-left:1px #ccc solid;padding-left:1ex">Hey all,<br>
<br>
After a long discussion with Aur Saraf, I think we came up with a good<br>
approach to join support in Persistent. Let's review the goals:<br>
<br>
* Allow for non-relational backends, such as Redis (simple key-value stores)<br>
* Allow SQL backends to take advantage of the database's JOIN abilities.<br>
* Not force SQL backends to use JOIN if they'd rather avoid it.<br>
* Keep a simple, straight-forward, type-safe API like we have<br>
everywhere else in Persistent.<br>
* Cover the most common (say, 95%) of use cases out-of-the-box.<br>
<br>
So our idea (well, if you don't like it, don't blame Aur...) is to<br>
provide a separate module (Database.Persist.Join) which provides<br>
special functions for the most common join operations. To start with,<br>
I want to handle a two-table one-to-many relationship. For<br>
demonstration purposes, let's consider a blog entry application, with<br>
entities Author and Entry. Each Entry has precisely one Author, and<br>
each Author can have many entries. In Persistent, it looks like:<br>
<br>
Author<br>
name String Asc<br>
isPublic Bool Eq<br>
Entry<br>
author AuthorId Eq<br>
title String<br>
published UTCTime Desc<br>
isPublic Bool Eq<br>
<br>
In order to get a list of all entries along with their authors, you<br>
can use the newly added[1] selectOneMany function:<br>
<br>
selectOneMany [AuthorIsPublicEq True] [AuthorNameAsc]<br>
[EntryIsPublicEqTrue] [EntryPublishedDesc] EntryAuthorEq<br>
<br>
This will return a value of type:<br>
<br>
type AuthorPair = (AuthorId, Author)<br>
type EntryPair = (EntryId, Entry)<br>
[(AuthorPair, [EntryPair])]<br>
<br>
In addition to Database.Persist.Join, there is also a parallel module<br>
named Database.Persist.Join.Sql, which has an alternative version of<br>
selectOneMany that is powered by a SQL JOIN. It has almost identical<br>
semantics: the only catch comes in when you don't fully specify<br>
ordering. But then again, if you don't specify ordering in the first<br>
place the order of the results is undefined, so it really *is*<br>
identical semantics, just slightly different behavior.<br>
<br>
Anyway, it's almost 1 in the morning, so I hope I haven't rambled too<br>
much. The basic idea is this: Persistent 0.5 will provide a nice,<br>
high-level approach to relations. I'll be adding more functions to<br>
these modules as necessary, and I'd appreciate input on what people<br>
would like to see there.<br>
<br>
Michael<br>
<br>
[1] <a href="https://github.com/snoyberg/persistent/commit/d2b52a6a7b7a6af6234315492f24f821a0ea7ce4#diff-2" target="_blank">https://github.com/snoyberg/persistent/commit/d2b52a6a7b7a6af6234315492f24f821a0ea7ce4#diff-2</a><br>
<br>
_______________________________________________<br>
web-devel mailing list<br>
<a href="mailto:web-devel@haskell.org" target="_blank">web-devel@haskell.org</a><br>
<a href="http://www.haskell.org/mailman/listinfo/web-devel" target="_blank">http://www.haskell.org/mailman/listinfo/web-devel</a><br>
</blockquote></div><br></div></div></div>