ActiveRecord interface to Arel is awkward, requires find_by_sql(query.to_sql)

rubyonrails-core
1

Hi,

I have a complex query that, as far as I can tell, either requires Arel or SQL. Performing Arel queries with ActiveRecord is awkward. Getting the query into ActiveRecord requires project(node_table.columns) and then find_by_sql(query.to_sql) There should be a method of ActiveRecord that accepts an Arel query, does the appropriate projection, and executes the query. Converting Arel queries to SQL defeats the purpose of Arel. Arel should not be dependent on SQL and should be able to work on no-SQL databases.

My entire query looks like this

  space_table = [1, 2, 3]   path = 'Layouts/Default'

  node_table = self.arel_table   dependency_table = Perens::Cms::Dependency.arel_table

  query = node_table \   .project(node_table.columns) \   .where(node_table[:space_id].in(space_list))   .join(dependency_table) \   .on(dependency_table[:node_id].eq(node_table[:id])) \   .where(dependency_table[:path].eq(path))

  dependents = self.find_by_sql(query.to_sql)

Core to this issue is the ActiveRelation vs. Arel dichotomy. ActiveRelation is Arel's poor cousin that can't measure up to it. It's an easier interface as long as your main query is where(field: value), but doesn't provide all of the functionality of Arel.

Thanks

Bruce

2

Hi Bruce,

Hi,

I have a complex query that, as far as I can tell, either requires

Arel or SQL. Performing Arel queries with ActiveRecord is awkward.

Getting the query into ActiveRecord requires

project(node_table.columns)

and then

find_by_sql(query.to_sql)

There should be a method of ActiveRecord that accepts an Arel query,

does the appropriate projection, and executes the query. Converting

Arel queries to SQL defeats the purpose of Arel. Arel should not be

dependent on SQL and should be able to work on no-SQL databases.

ARel is a SQL builder language designed for relational databases. It is used to construct queries which are then executed on a database by using something such as find_by_sql. It will not work on no-SQL [sic] databases because it is designed for relational databases, which is not what a no-SQL [sic] database is!

The syntax provided by ActiveRecord::Relation is designed to provide shortcuts to most of the “cool” methods inside of ARel, and I acknowledge that it’s a bit lacking at times.

However, I think your query can be done in that syntax. I am not sure what relation node has to dependency, so you may need to change :dependencies in the joins here to :dependency

Node.where(:space_id => space_table).joins(:dependencies).where(“dependencies.path” => path)

The code above will return an ActiveRecord::Relation object and when you iterate over it or call to_a or inspect on it, it will return the resultant objects.

Core to this issue is the ActiveRelation vs. Arel dichotomy.

ActiveRelation is Arel’s poor cousin that can’t measure up to it. It’s

an easier interface as long as your main query is where(field: value),

but doesn’t provide all of the functionality of Arel.

If you can provide exact use cases in how to improve ActiveRelation rather than this childish “OMG ACTIVERELATION SUCKS!!!”, that would be most appreciated.

Thank you.

3

Hi Bruce,

Arel does not really belong to the public interface of Active Record. It happens to be used internally, but in theory Active Record could switch to something else tomorrow.

Some few methods like arel_table are published in the API, but I believe there is consensus in core that they are not public interface either and should be nodoc’ed. (Please someone correct that otherwise.)

4

Hi Ryan and Xavier, It’s true that the available “no-SQL” databases are also non-relational, and that’s not what I’m after. It doesn’t seem to me that Arel must always emit SQL, though. The syntax is sufficient to control a relational database directly. Arel was one of the most promoted features of rails 3. Perhaps this wasn’t your intent, but it’s so. In contrast, these are the only methods of ActiveRecord::QueryMethods with a word of documentation on api.rubyonrails.org: extending, reorder, select, uniq. Even where has no documentation. joins() is similarly undocumented. And when one figures out ActiveRecord::QueryMethods, it’s only to find that many of the methods require SQL fragments as arguments and aren’t particularly portable across databases. If you look around the net for help on doing complex queries with Rails 3, the instructions are to drop into Arel and then feed it back to ActiveRecord.

With all due respect, you seem to have a disconnect with your users.

5

Arel was one of the most promoted features of rails 3.

Not really, what was promoted from Rails was that Active Record had a new interface thanks to its new integration with Arel (Pratik Naik did that work). It is true, however, that in the early days some people misunderstood the role of Arel for end-users, due to blog posts and stuff that is outside the control of the project.

But I think generally speaking that is clear nowadays. In particular the current edition of AWDwR, Rails 3 in Action, and The Rails 3 Way do not even mention Arel except in some glossary or in passing. They cover the Active Record interface.

Perhaps this wasn’t your intent, but it’s so. In contrast, these are the only methods of ActiveRecord::QueryMethods with a word of documentation on api.rubyonrails.org: extending, reorder, select, uniq. Even where has no documentation. joins() is similarly undocumented. And when one figures out ActiveRecord::QueryMethods, it’s only to find that many of the methods require SQL fragments as arguments and aren’t particularly portable across databases. If you look around the net for help on doing complex queries with Rails 3, the instructions are to drop into Arel and then feed it back to ActiveRecord.

Yeah, this guide is certainly better

http://guides.rubyonrails.org/active_record_querying.html

though the API should not only have more content than the guide, but should be the comprehensive reference. That should be fixed.

With all due respect, you seem to have a disconnect with your users.

Naahhh, appreciate your hypothesis, but I am pretty sure that is not the case generally speaking.

6

It's true that the available "no-SQL" databases are also non-relational, and that's not what I'm after. It doesn't seem to me that Arel must always emit SQL, though. The syntax is sufficient to control a relational database directly.

Perhaps some history may provide a context for this discussion. Originally Arel was developed by Nick Kallen as a relational algebra engine for Ruby. The original implementation had an ActiveRecord/SQL engine but I believe that some alternative engines were developed by other people (LDAP springs to mind for some reason). Nick lost interest/moved on to other things (I believe he was involved in improving Twitter's messaging platform) and Bryan Helmkamp developed it further.

At some point (I don't know when) it was decided that rather than constructing SQL out of string fragments, Rails 3 would use Arel to build SQL but with a separate query interface that maintained backwards compatibility but added functionality (ActiveRecord::Relation). However because Arel was already quite well known by that point some people got the wrong end of the stick, especially with the protracted development of Rails 3.

Once Rails 3 was out there were reports of slow query performance which turned out to partly down to Arel. To solve the problem Aaron Patterson re-wrote Arel for version 2.0 to focus on generating SQL as fast as possible - a lot of the abstract relational algebra features were removed because Rails didn't need them and they slowed things down. At this point Arel is really just a component of Rails - it's development is pretty much directed by what Rails needs. The original implementation by Nick Kallen is still available on GitHub at GitHub - nkallen/arel: A Relational Algebra if you're interested.

Arel was one of the most promoted features of rails 3. Perhaps this wasn't your intent, but it's so. In contrast, these are the only methods of ActiveRecord::QueryMethods with a word of documentation on api.rubyonrails.org: extending, reorder, select, uniq. Even where has no documentation. joins() is similarly undocumented. And when one figures out ActiveRecord::QueryMethods, it's only to find that many of the methods require SQL fragments as arguments and aren't particularly portable across databases. If you look around the net for help on doing complex queries with Rails 3, the instructions are to drop into Arel and then feed it back to ActiveRecord.

ActiveRecord::Relation is designed with the Pareto principle in mind - the majority of web apps have relatively simple querying requirements most of the time and when you do need to do something complex then you can drop down to SQL. As for the issue about database independence, well how many web apps have to be database independent to the degree where you can just flip a switch. If you're writing an app that can be deployed on top of different database then you can special case each query - it's likely to be only a few queries anyway.

TL;DR - Rails is a framework for building real web applications and not a computer science research project. Sometimes we do things which aren't the 'right' way but it's usually with the best of intentions.

With all due respect, you seem to have a disconnect with your users.

Actually, I'd say the opposite - there was a widespread concern amongst users that Active Record in Rails 3 was slow and it was preventing them from upgrading. We addressed that concern by developing a new version that focussed on what it was being used for and not some theoretical use.

Andrew White

7

Could it be better if ActiveRecord users were completely unaware of Arel? i understand it was an unwanted side effect of the adoption of a well known component, but there are even some simple situations in which i usually resort to Arel, or at least I'm tempted to do so.

Just a little example:

    term = params[:term]          @tags = if term.size >= 3       condition = ActsAsTaggableOn::Tag.arel_table[:name].matches("%#{term}%")       ActsAsTaggableOn::Tag.where(condition).pluck(:name)     end ||

    render :json => @tags

Say that on average i resort to arel a couple of times for each rails application i wrote.

I would like to ask you if it's only an historical problem of people learning bad manners around the internet or if ActiveRecord querying interface could be improved somehow in order to make it the first choice in place of Arel (and possibly also deprecate the direct use of Arel).

Going on like this would probably lead to problems in case you choose to drop Arel in future.

(I hate to admit it but sorry for my english I still can not express myself well, despite how hard I try)

Maurizio

8

I don't remember any open discussions in Rails-core on the plans for the future of ActiveRecord when Arel was chosen.

I'd have suggested Sequel instead. Great documentation and performed much better than current AR when I switched a while back...

I'm not sure if it was even considered by that time...

I'd really appreciate that goals like this were discussed in this group before they got implemented.

This is just a suggestion, of course.

Cheers, Rodrigo.

9

Coming from the perspective of an ActiveRecord adapter like SQL Server, I wish that both ActiveRecord and plugins alike used Arel more heavily. I know this is a doubled edged sword. You want end users of the framework to stay as high level as possible and not dip down to Arel. However, I see a need to expose Arel more for plugin developers to stop constructing SQL fragments from strings.

The biggest pain from my perspective are order strings and select fragments. The SQL Server adapter’s visitor needs to leverage real Arel ordering nodes since the visitor will have to eventually remove duplicates as TSQL does not allow “ORDER BY foo ASC, foo DESC”.

Other issues always come back to the way our adapter has to handle LIMIT and OFFSET using a paging function like ROW_NUMBER(). When plugin authors even use basic of SQL fragments like a SELECT… vs projections, it means we have to end up doing the parsing instead as we have to re-compose a complex statement to work with our window functions.

The way I see, the more ActiveRecord and its plugins use Arel, the better off other adapters and engines can make decisions lower down the stack. I hope Rails core considers this as we move forward.

  • Ken
10

If a similar problem exists I purpose instead the adoption of a middle layer of abstraction for common complex queries in a way similar to what built-in helpers are for views.

A very stupid API stub could be like that

module ASetOfCommonComplexQueries

def a_very_common_complex_query(*params)

… Use a lot of AREL here

then return an ActiveRecord::Relation

end

end

It’s just an example, you can probably do much better

This layer could also provide the necessary abstraction to these queries that for some reason depends on a particular database:

module Pg::ASetOfCommonComplexQueries

def a_very_common_complex_query(*params)

a_very_common_complex_query implemented with pg related features

end

end

module Mysql::ASetOfCommonComplexQueries

def a_very_common_complex_query(*params)

a_very_common_complex_query implemented with mysql related features

end

end

Obviously these implementations are responsibilities for adapters more than for Rails but a common convenience API could be a way to approach the problem.

I really dislike the idea that client code should depend on an implementative component.

Maurizio

11

Xavier wrote:

though the API should not only have more content than the guide, but should be the comprehensive reference. That should be fixed. I can’t over-emphasize this. If you want users to make use of an API preferentially over another, the API documentation must be present. Not documenting a method will confuse users: is it internal? Is it meant for our use at all? For joins and where to be undocumented this way really is failure to communicate your intention to users. Rails Guides like Active Record Query Interface — Ruby on Rails Guides are tutorial rather than canonical. We don’t expect them to explain the full functionality of an API, and we are left with either experimentation or overly time-consuming deep dives into source as the only way to determine what the API really does, and then we have no guarantee that it will still do that in the next version. And much as we appreciate the good books on Rails, they aren’t supposed to be the core developers official statements on the API. Andy White wrote: well how many web apps have to be database independent to the degree where you can just flip a switch. My code is intended for widespread distribution on the net and installation and operation by others without the developer’s support. Not every Rails app is a single-site thing. So, database independence is important. Maurizio wrote: I would like to ask you if it’s only an historical problem of people learning bad manners around the internet or if ActiveRecord querying interface could be improved somehow in order to make it the first choice in place of Arel (and possibly also deprecate the direct use of Arel). The original concept of Arel was to duplicate the operators of SQL without departing from the programming language we were already using, by proving a query algebra in Ruby. That’s a powerful concept. It’s unfortunate that Aaron had to remove some operations to make it fast, but it’s still an algebra. The current Active Record Querying interface is, in contrast, a shorthand for SQL which, combined with some SQL fragments in strings, can generate 80% of the queries that people will need. So, what is our recommended path for users once they are writing those 20% of queries that Active Record Querying operators aren’t designed to cover? You can expect them to have to do so at least once in every large application.

It’s either fall into SQL, or use Arel.

In Arel’s favor, it’s portable across databases, it covers most of the ground (although not everything that can be stated in SQL for a particular database), it’s in the language we were already writing in, and it is arguably a clearer syntax than SQL because SQL is little changed since Codd’s work in the late '70’s.

In SQL’s favor, it’s well known and it can express everything that the connected database can do.

12

That was exactly the point where I moved from AR to Sequel. I reached one of the 20% of the queries (that happens to be more than that in my case) where AR interface is too limiting for my application:

https://groups.google.com/forum/?fromgroups#!topic/rubyonrails-core/XxmaSvOkgO4

Lack of documentation, support, and a good-enough API. All of that

I’ve found in Sequel and it further performed better than AR, so yay! :wink:

Pretty happy with the move and I sincerely wish the best of luck for

AR to get over the “80%” it currently supports…

Cheers,

Rodrigo.
13

Sequel looks interesting. At first glance, it looks more mature than AR, and I guess shows how AR might evolve. Of course, I know nothing about its performance, etc. I might try it in my next application.

14

Sequel looks interesting. At first glance, it looks more mature than AR, and I guess shows how AR might evolve. Of course, I know nothing about its performance, etc. I might try it in my next application.

Yes, AR might evolve. Sequel has intrigued me as well, but relying on unofficial solutions scares me a little, even if it has a good support.

My concern stems more from the fact that the design of Rails sometimes exposes not-too-defined boundaries (ACTIVERECORD/AREL/SQL is only an example), that sooner or later will be defined, i hope.

Maybe this arises from the fact that Ruby does not have a built-in construct for interfaces, but i can’t say that for sure. In any case I do not know if replacing a key component of the framework with a third party one is a wise choice in the long run. I can’t be sure that pieces will fit always good.

Maurizio

15

Squeel (not to be confused with Sequel) does a pretty good job of covering the other 20%.

https://github.com/ernie/squeel

Allen Madsen http://www.allenmadsen.com

16

Rodrigo Rosenfeld Rosas:

Lack of documentation, support, and a good-enough API. All of that

I’ve found in Sequel and it further performed better than AR, so yay! :wink:

Pretty happy with the move and I sincerely wish the best of luck for

AR to get over the “80%” it currently supports…

Allen Madsen:

Squeel (not to be confused with Sequel) does a pretty good job of covering the other 20%.

https://github.com/ernie/squeel

Allen Madsen http://www.allenmadsen.com

I can agree that there may be better solutions to replace / complement ActiveRecord but I believe that now is the moment to define a query interface that meets the requirements of our applications, in a way that is not intended to change for a long time and that can be shared between different gems / libraries / ORMs, so that the rest of the implementation can safely change across the time (Arel, Squeel, Sequel or any other brilliant solution that is right for our).

Maurizio

17

Sorry, but I didn’t get it. Sequel shares nothing with AR as far as I can tell you and hasn’t significantly changed its interface for years I guess…

For example, Sequel is not likely to support dynamic methods or

method_missing for one. You shouldn’t expect to find something like record.created_at_changed? in Sequel core, for example, among other “magic” methods, like the now deprecated find_by_xxx dynamic finders.

So, it doesn't rely on AR API and it won't change if AR does.

Did I miss something?

Best,

Rodrigo.
18

I can totally understand your point of view as I also share it and taking the decision to migrate from the default AR to another solution was not that easy as you might expect.

I found that MongoDB is better supported by the Rails community than

Sequel, so I had to write some integration code by my own, like the sequel-devise gem. And I had to look at the source code of Devise as it wasn’t clearly documented what such an adapter should provide and it took some time, but not that much as you could expect. I’ve also had to add a “save!” method to Sequel::Model for being able to use FactoryGirl and a few other adjustments but I wouldn’t say it was a pain to get my preferred tools (like RSpec) to gracefully integrate to Sequel, although it certainly took some time.

I've wrote about some of the solutions I've written, but I'm

planning to post some more interesting features when I find some time:

http://rosenfeld.herokuapp.com/en/articles/2012-04-18-getting-started-with-sequel-in-rails

Among the features I haven't written about yet are the possibility

of running my development (or any other) environment inside a transaction inspired by the “rails c --sandbox” option.

But actually the "--sandbox" is half-baked while my solution is not:

https://groups.google.com/forum/#!topic/rubyonrails-core/fAhmhaevKQY

Other than that I was able to use transactions and PostgreSQL

savepoints even in RSpec beforeAll/afterAll hooks, which I just can’t do in out-of-the-box RSpec + AR setup.

Using transactions this way is not supported by Sequel as RSpec

doesn’t provide an aroundAll hook, but I could get fast and great support from Jeremy Evans, the current main maintainer of Sequel. He helped me to get an unsupported solution that would help me with the sandbox and transaction support in beforeAll/afterAll.

This not only made my integration specs run much faster (truncate is

real slow in PostgreSQL for testing purposes and transactions are much faster then lots of DELETE statements that must be issued in a certain order due to foreign key constraints). It also made my specs easier to read.

And running "SANDBOX=1 rails s" is really helpful when I'm

experimenting with a new feature or trying to reproduce a bug and have a copy of the production database that I don’t want to mess in my development environment. So every single change would be rolled back when I hit Ctrl+C. This is awesome.

Of course, I have only tested this on PostgreSQL which also happens

to be the database vendor of choice of most Sequel users, differently from AR that seems to be better target at MySQL. I don’t know if MySQL would support savepoints, so maybe my solution won’t fit everyone’s need.

But this is another reason why I chose Sequel. I don't like MySQL

and I don’t think AR gets some things right. For example, Sequel thinks that the database should be used in full, with ACID support and that your constraints are very important. It also encourages to write triggers when appropriate among other things. And I’ve always shared those same ideas. This was always something I disliked in AR and how it doesn’t seem so much worried about such constraints in the sense that you write “t.references :something” and it won’t create a foreign key to it as well as an index. At least it is how it has been over the last years. And I think this happens because the original authors prefer MySql and in the last years it bundled with MyISAM engine set to the default and this situation has just changed this year.

This tells me a lot. For example, in my humble opinion, it shows me

that the original authors didn’t know about databases as much as the database expert people I admire. Or they wouldn’t have chosen MySql in the first place. Also it tells me that they don’t care about database constraints, indices and transactions as much as I do. It seems that they started writing something and when they got more clients and the queries got slower they realized that they should create an index, for example. Maybe I’m just guessing here, but this is definitely not the way I see databases.

Sequel just seems more aligned with my own ideas and even if I had

to take some time to adapt it to the gems I’m using (Devise, FactoryGirl, RSpec) I can say it totally worthed my little time spent on this integration and it has saved me a lot of work trying to figure out how to do some tasks with AR, specially when you can’t find much documentation about it.

I'm not advocating everyone should replace AR with Sequel. It will

really depend on what project your ideas are more aligned with. I can say that Rails itself integrates very well to Sequel, although you might have to write some code to integrate to other gems. And I don’t think it would be likely to break your application due to some Rails upgrade because Sequel would be no longer supported as it doesn’t require any integration to Rails itself.

But such decision is up to you and I'd advice you to spend some time

reflecting on it for your next project because the ORM is a key library in most applications, specially usual web ones.

I'm not currently considering using anything other than Sequel for

any serious new project I decide to create and can take this decision. If you decide for Sequel though, just let me know if you need any assistance and I’ll gladly help you. You can also send any questions to sequel-talk and it’s likely that you get a response in within 24h.

Cheers,

Rodrigo.
19

No you didn’t. In fact i was not speaking of AR changes but the way rails interacts with it. If I’m not wrong Sequel needs different plugins from AR’s ones or at least alternate versions of AR plugins. It would be probably a good thing if AR would have a stable and definitive interface capable to cover any reasonable use case, then probably you can use an universal adapter for any AR plugins in a way you can’t even notice it and thus filling the gaps between AR support and Sequel.

Maurizio

20

Sorry, but I have no idea what you’re talking about. Could you please give me an example of one of those AR plugins you’re referring to?