ActiveRecord Query `[]` Function

rubyonrails-core
1

I am going to make this quick. Try something like: User.all[5] Rails presently runs the following query in Postgres: SELECT “users”.* FROM “users” And then gets the 6th element from the array of results.

In reality, User.all[5] should be equivalent to: User.limit(1).offset(5).first in all circumstances that this addition to the query can be performed. This means that someone looking for the nth User, for example, can simply use the brackets as is idiomatic in Ruby, to perform the query which is truly desired by the user.

2

What do you expect to happen if the code looks like this:

User.all[5] User.all[6] User.all[7]

Should ActiveRecord make three separate queries? How about if the code iterates from index 100 to 200?

Imho, it’s totally acceptable to sacrifice ‘idiomatic’ ruby in this case in favour of fewer accidental gotchas.

Olly

3

Your example would involve three queries, each only allocating memory and transferring content from the DBMS for 1 result, instead of every record in the table.

In the present: User.all[5] # SELECT “users”.* FROM “users” → 420,000 results

My suggestion: User.all[5] # SELECT “users”.* FROM “users” LIMIT 1 OFFSET 5 → 1 result

If I understand right, every User is pulled from the database, an array is created with every single possible instance of a User, and then you select a specific value from that array. I am thinking about this in the context of any query. If you want the nth result from that query, it would be nice to have an idiomatic shorthand for that, instead of “.offset(n).first”.

It appears that even if you are iterating over the elements in a query in such a way, my suggestion would improve performance. This has nothing to do with any of the other functions that are delegated to Array. This is for an ActiveRecord query that one is looking for the nth element within those query results.

No one in their right mind would use the delegated ‘’ function on a query that could have thousands of results. But they would call the ‘’ function that I am suggesting.

4

The whole point of the all method is that it actually fires the query on the current relation. So 1.) a change like this could potentially break a lot of existing code and 2.) it would defeat the point of the method. So I strongly believe that it shouldn’t change. If you want the limit/offset query, use those methods. That’s why they’re there.

5

I disagree with the addition of all[X] but isn’t all supposed to not fire the the query and return some kind of ActiveRecord::Relation now ? If so, the [] addition would be made on it and I don’t think it would be a good thing :confused:

Simon Courtois

6

I agree very much with James and would add that if iteration was done in this manner it would not improve performance since the additional queries add round-trip time (latency, db query setup, etc) which typically exceeds time spent returning results, especially in multi-tier environments. In my environment with 270k user rows, calling User.offset(x).first with five ID’s varying by 50000 was 23% slower than calling User.all. I imagine this would be far worse for larger iteration strides.

As far as idiomatics are concerned, all returning an eagerly-loaded array is more idiomatic to me than all returning a proxy that quacks like an array but lazily-loads items. The current implementation of all makes working with a cohesive or related set of objects trivial, especially in the face of database contention. What happens if rows change while my application is making a bunch of queries for individual records? I would either risk getting inconsistent data or I’d have to create a locking scheme.

These all seem like high prices to pay for a debatable idiomatic change.

7

“As far as idiomatics are concerned, all returning an eagerly-loaded array is more idiomatic to me than all returning a proxy that quacks like an array but lazily-loads items.”

Particularly given that relations are already themselves proxies that generally quack like arrays but lazily-load items. The all method is specifically there so that you can break out of that proxy.

8

As of Rails 4, Model.all returns an ActiveRecord::Relation, not an array - that now requires a specific call to to_a on a Relation.

This being the case, how about allowing array-like syntax equivalent to both offset andlimit methods? I’d quite like e.g.:

Item[0] == Item.limit(1)
Item.all[2..4] == Item.offset(2).limit(2)
Item.all[4,4] == Item.offset(4).limit(4)
Item.order(:size)[5,10] == Item.order(:size).offset(5).limit(10)

… and so on. Don’t think that conflicts with any other syntax?

Happy to PR if anybody likes this.

-Matt

9

My thoughts for this are divorced from the “all” method. It was just my poor example of a query. Perhaps, a better example would be:

User.where(‘age > 18’)[4]

or as suggested, ranges could be used as well:

User.where(‘age > 18’)[0…5]

Perhaps the only problem that I see, is if someone stores a query and then iterates over the results using this method, they will likely result in unnecessary load. I guess if this were to be a feature, one would have to keep this in mind, and gems may have to account for it. I am not sure yet if it could work without negatively affecting much. I would think that if the ‘’ function is called on a single query instance, multiple times, passing in a single integer, that would indicate that the user is expecting a different behavior. Still, it is very possible that this would also be unreasonable.

10

Regardless of whether or not it might be a nice idiomatic style to be able to have, I think this discussion comes back around to what a lot of others ones on this list do as well: the change would be a pretty big potentially breaking change to existing code. So there’s no real good reason to make the change to existing functionality.

Also, this could introduce many unexpected consequences (even ignoring the potential n+1 queries) given that the DB isn’t locked around access. So unlike a prefetched array result set, what’s in the “list” could change underneath you which would certainly be unexpected behavior.