ActiveRecord .order and .first/.last

rubyonrails-talk
1

Hello, I’m fairly new to Ruby and Rails and I’ve been playing around for a while with Rails and Turbo. Currently I’m trying to dynamically add records to a table that is populated directly from a list of entries from the database. As the dynamically entries should be added ordered by date, I’m trying to “find” the right place to insert the new record based on a custom ‘date’ field within the entity.

The following is the code I’m using to try to get the entries whose “transaction_date” is before the “one” I’m trying to insert.

pocket.transactions.order(transaction_date: "asc").where("transaction_date < ?", "2024-02-04").last

If I run the code without the .last call I would get somethinkg like

  1. 2024-02-01
  2. 2024-02-03
  3. 2024-02-03
  4. 2024-02-03

My expectation when calling .last is that the entry 4 is the one returned. But instead i’m getting entry 2 So I’m wondering if I’m missing something with this call

Thank you in advance for your help

2

Interesting. Did you try ordering after the where clause. I ask because I’d expect things that way in a SQL query:

SELECT * FROM table WHERE where_clause ORDER BY the_order;

Whereas if you order before your where, I’m imagining AR is grabbing all the entries, ordering them and then trying to do a where on that selection… I suppose the only way of knowing for sure is to print out the generated SQL using to_sql, for example

3

Note that your last three records have the same transaction date, so the date alone is not unique enough for this to get ordered properly. If you add another more unique thing in the mix then it should work. Let’s also include the ID:

pocket.transactions.order(:transaction_date, :id).where("transaction_date < ?", "2024-02-04").last

(Also note that “ASC” is the default for .order since it is SQL’s default for ORDER BY.)

2 Likes
5

Continuing the discussion from ActiveRecord .order and .first/.last:

Thank you for your replies.

@mateusdeap

Interesting. Did you try ordering after the where clause. I ask because I’d expect things that way in a SQL query:

I actually did and the result was the same.

@Lorin_Thwaits What you mention makes sense from the DB point of view and if the list is unsorted once retrieved from the database. I am not sure how the internals of rails when retrieving a relationship are. Is it lazy loaded, eagerly loaded and if either, will further operations on the list cause a reload?.

I.e. If I do the following:

transactions = pocket.transactions.order(transaction_date: "asc").where("transaction_date < ?", "2024-02-04")

the transactions variable would hold a list with the following elements in this order:

  transactions[0] # 2024-02-01
  transactions[1] # 2024-02-03
  transactions[2] # 2024-02-03
  transactions[3] # 2024-02-03

I would expect that if if do

transactions.last

The retrieved element would correspond to transactions[3] as the .last method is not really operating on SQL or database related logic, but on the list indexes (I may be wrong though). Instead, what I get is the element that correspond to transactions[1] which is why I found this behavior kind of weird.

6

Note that the database doesn’t have a concept of “first” or “last”, just “select a number of rows”. transactions.order(transaction_date: "asc").last is functionally identical to transactions.order(transaction_date: "desc").first, both will query ORDER BY transaction_date desc limit 1 (you should be able to check query logs to confirm).

When sorting items, the database (and also Ruby itself) runs a stable sort, so objects with the same value will be kept in their current order. If you pluck(:transaction_date, :id) from your “asc” query, you might get this result:

[["2024-02-01", 1],
 ["2024-02-03", 2],
 ["2024-02-03", 3],
 ["2024-02-03", 4]]

Now try order(transaction_date: "desc") and the database will happily see that record 1 should be sorted after the other ones, but since the other ones are all the same value and you’re only ordering by date, it won’t distub their order and you’ll get:

[["2024-02-03", 2],
 ["2024-02-03", 3],
 ["2024-02-03", 4],
 ["2024-02-01", 1]]
7

Thank you for your reply.

I get the database logic. I guess what confuses me is that I expected that when doing

transactions = pocket.transactions.order(transaction_date: "asc").where("transaction_date < ?", "2024-02-04")

further calls to .last on transactions variable would operate on the resulting array, thus making transactions.last result in always returning the element in the last index. I guess I didnt understand .last as a sorting related method as I wasn’t expecting it to “sort” rather than retrieve what was already sorted.

It seems that is not the case and in my example transactions would still be tied to the ActiveRecord “context” and so .last will still perform a sort on that array?

8

Ah, that might be the missing distinction then. The value returned from where is not an array, but is an ActiveRecord::Relation object ready for further chaining methods. When you call last, that is not getting the last element of an array but is triggering the final SQL query to the database.

Compare with a version that’s explicitly using all to turn the relation chain into a result array:

pocket.transactions.order(…).where(…).last
pocket.transactions.order(…).where(…).all.last

The latter works the way you were expecting.

1 Like
9

awesome. I think what I was missing is that call to .all Rails is deffinitely unique in certain ways :smile:

Thank you all for your help

10

Ya @juankprada, the cool idea behind an ActiveRecord::Relation is somewhat that “things do not exist until they are observed”. It’s just an object-oriented representation of a query – and even calling .all does not yet hit the database – it’s only when you finally view that thing with .inspect or otherwise that it gets executed. You can ask for results with things like .first or .pluck() or .to_a, and finally at that point the Arel AST tree is walked, which builds a final SQL query and then runs it.