[std.database]

[Jacob Carlborg]

On 2011-10-12 15:45, Andrei Alexandrescu wrote:
> On 10/12/11 2:36 AM, Jacob Carlborg wrote:
>> I usually prefer calling methods and functions instead of writing
>> everything in a string literal.
>
> I guess reasonable people may prefer differently. Someone who knows SQL
> may prefer it instead of learning another API, with its own inevitable
> quirks and limitations.
>
>> These forms might not make a big difference but when you use an object
>> oriented API I think it does.
>>
>> Take this Ruby example:
>>
>> Post.joins(:comments => :guest)
>>
>> Produces the following SQL:
>>
>> SELECT posts.* FROM posts
>> INNER JOIN comments ON comments.post_id = posts.id
>> INNER JOIN guests ON guests.comment_id = comments.id
>
> That's a good illustrative example. My understanding is that some setup
> is needed in Ruby (defining the classes and associations) so the
> one-liner doesn't tell the full story. One needs to learn a fair amount
> of specifics to do that, whereas I know what your SQL query does today.
> I'd write it in a terser manner like this:

Yes, exactly. But everything depends on what you want to do and what 
your application does. The thing is that your application doesn't have 
to do much before you want to start using class and objects to implement 
it. And if a framework can load a SQL result into a set of objects, 
what's wrong with that.

> SELECT a.* FROM posts a
> JOIN comments b ON a.post_id = b.id
> JOIN guests c ON b.id = c.comment_id
>
> I read through http://guides.rubyonrails.org/association_basics.html
> and, well, one way or another one needs to learn relational algebra to
> work with it, be it in an SQL form or a Ruby form. One possible issue
> is, what happens with parts of it that aren't properly covered. For
> example, to take the difference between two sets, I'd use:
>
> SELECT a.* FROM table1 a
> LEFT OUTER JOIN table2 b ON a.id = b.id
> WHERE b.id IS NULL

I think you would have to put the join in a string, like this:

Table1.joins("LEFT OUTER JOIN table2 b ON a.id = b.id").where("b.id IS 
NULL")

As the link says:

Using Array/Hash of Named Associations
This method only works with INNER JOIN.

But I don't see why it wouldn't be possible to to something like this:

Table1.left_outer_join(:table2).where("b.id IS NULL")

At least for this example.

> (There are a couple other alternatives depending on DBMS.) This is an
> SQL classic, but I wouldn't know how to express that with the Ruby API.
> And that doesn't stop here. Many interesting queries are just as terse
> as they need in SQL and I fear would look convoluted when forced through
> another API.
>
>
> Andrei

Yes, you would need to execute raw SQL, or parts of a query in raw SQL, 
as the join example above shows.

An ORM API is not limited to an SQL database, it can be used for other 
things as well. ActiveResouce provides similar API to ActiveRecord but 
connects to a RESTful API instead of a database.

http://api.rubyonrails.org/classes/ActiveResource/Base.html

It's probably possible to adapt an ORM API to have a NoSQL backend as well.

I don't know how you code your applications but I usually code mine in 
an object oriented way. SQL and object orientation don't fit really good 
together. Because of that it's nice to have an ORM layer that hides the 
ugliness of converting SQL results to objects and I can use the objects 
just as they where created like "regular" objects with no database 
connection.

-- 
/Jacob Carlborg