Rails 3: LINQ-like features?

In Microsoft's LINQ after you define your model, you can say something like this:

   var products = from p in products where p.category.name == "Books" select p;

This gets translated in the following SQL statement (roughly)

   SELECT * FROM products LEFT OUTER JOIN categories ON categories.id = products.category_id where categories.name = "Books"

Since we already have the model mostly defined through has_many/ has_one/belongs_to/etc. declarations, wouldn't it be nice if we could say:

   products = Product.find(:all, :conditions => ['category.name = ?', "Books"])

which would then generate the left outer join query.

Using ezfind plugin this would be fairly straight forward as it uses a
block and some kind of magic

I couldn't find any information about any ezfind plugin for Rails.

Talking about LINQ,

Product.find(:all, :conditions => ['category.name = ?', "Books"])

is not really LINQ which means language integrated query. What would be really LINQ-like is:

Product.all.select { |p| p.category.name == "Books"} Article.all.reject { |a| a.comments.count == 0 } Person.all.find { |p| p.roles.include? Role.find_by_name("manager") } etc.

I've written an extension to ActiveRecord that adds the magic "all" method to a model class that supports select, reject, find, each etc. I haven't had time yet to implement joins though. I've also realized that it is not possible to implement fully language integrated query in Ruby as some operators/constructs are implemented at the language level and cannot be overriden, such as the != operator, the if/unless statement/expression and the not operator. The following LINQ-code, as I've experienced, is not possible: Product.all.select { |p| p.name != "Funky Trousers" } or: Article.all.each { |article| puts article.title if article.comments.count > 0 }.

It is possible to substitute !=, not and if with alphanumerically named methods such as "not_eq", "not" and "if_true" (a la Smalltalk), but it wouldn't be 100% "language integrated".

Erik

Erik,

    > Product.find(:all, :conditions => ['category.name = ?',
"Books"]) is not really LINQ

True, although it's one of LINQ's more useful features and it would
be a big step forward for Rails.

I wonder if it would be possible to create a class that essentially
creates an RPN-based log of how it was executed     Product.select { |p| p.category.name == "Book" }

So select would yield with argument p as a SqlLogger class.    - p.category would invoke log something like " push_property
'category' " and return itself (p)

   - (previous_result).name would invoke the same " push_property
'name' " and return itself (p)

    - "Book" would be ignored (which at first seems like a problem,
but it gets solved in the next line:)

   - (previous_result).<=> would recognize that its parameter is a
string, so it would first call " push_value string " and then call "
push_comparison ". If the parameter is of type SqlLogger, then the
expression will already have been pushed on the stack so it can only
call "push_comparison". Now the problem is, how do we know whether
<, <=, ==, >=, or > was called... and we don't. But assume we can
solve this, let's continue.

So the called after select called yield, its result would be p, the
SqlLogger which now has an RPN-based execution log, which could then
be used to generate the SQL.

After writing all this, I realize that you probably went through the
exact same thing (but in more detail) when writing your class, and
there might not be a solution for if/unless/== etc. Maybe the non- integrated string parsing is a better solution because there would be
fewer subtleties.

- Mike

Here are some good links to get started.

http://brainspl.at/articles/2006/01/30/i-have-been-busy http://onestepback.org/index.cgi/Tech/Ruby/Criteria.rdoc http://lafcadio.rubyforge.org/manual.html#id801866

I'm not really sure rails-core is the right place to discuss it. Something like this would probably need a solid implementation for AR that everyone likes before we can think about committing it to rails. Something like the #all method should be easy to do as a plugin.

I've written stuff like this in python, but I feel that it's just not worth the effort for the more complex queries. ez_where is pretty cool, but I only use it in very rare instances. That's just my 2 cents I guess...

Mike,

Your example sounds more like a kind of object query language.

Rick,

Lafcadio, ez_where and the Criteria Library don't really make queries as transparent as filtering a Ruby array. Lafcadio seems to be closest to that though.

I'm aiming for something like

Product.all[10..99].sort_by(:name).sort_by(:price).reverse.select { |p| ... }

And saving and deleting should be as simple as doing

Product.all << Product.new

and

Product.all.delete(some_product)

Erik

Have a look at Erlang's Mnesia: it goes back to the roots of SQL as a set-based language, and uses list comprehensions as queries.

Dave

You could also have a look at http://muermann.org/ruby/criteria_query

--max

Sorry, if I am missing something here. But wouldn't it be perfectly legitimate with today's implementation to do

products = Product.find(:all, :include => :categories, :conditions => ['category.name = ?', "Books"])

Just my .02

Cheers, Martin