[std.database]

[Andrei Alexandrescu]

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:

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

(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