Rails Query Through Association Limited to Most Recent Record

Rails query through association limited to most recent record?

If you aren't going to go with @rubyprince's ruby solution, this is actually a more complex DB query than ActiveRecord can handle in it's simplest form because it requires a sub-query. Here's how I would do this entirely with a query:

SELECT   users.*
FROM     users
         INNER JOIN books on books.user_id = users.id
WHERE    books.created_on = ( SELECT  MAX(books.created_on)
                              FROM    books
                              WHERE   books.user_id = users.id)
         AND books.complete = true
GROUP BY users.id

To convert this into ActiveRecord I would do the following:

class User
  scope :last_book_completed, joins(:books)
    .where('books.created_on = (SELECT MAX(books.created_on) FROM books WHERE books.user_id = users.id)')
    .where('books.complete = true')
    .group('users.id')
end

You can then get a list of all users that have a last completed book by doing the following:

User.last_book_completed

Include only the latest/newest associated record with active record?

This is a working scope with lambda to pass parameters to the scope to select the genre id.

scope :latest_with_genre, lambda do |searched_genre_id|
  joins(:books)
    .where('books.date_of_publication = (SELECT MAX(books.date_of_publication) FROM books WHERE books.author_id = authors.id)')
    .where("books.genre_id = #{searched_genre_id}").group('author.id')
end

This answer Rails query through association limited to most recent record? from
Pan Thomakos helped me for the scope.

This answer Pass arguments in scope from keymone helped me for passing argument

How to fix this n+1 query: Limiting child association to most recent record in JSON api response

One solution is to define a has_one with an association scope:

has_one :most_recent_task, -> { order(created_at: :asc) }, class_name: "Task"

You can then use includes to eagerly load the data:

>> Project.includes(:most_recent_task).all
  Project Load (0.3ms)  SELECT  "projects".* FROM "projects" LIMIT $1  [["LIMIT", 11]]
  Task Load (0.5ms)  SELECT "tasks".* FROM "tasks" WHERE "tasks"."project_id" IN (1, 2) ORDER BY "tasks"."created_at" ASC

Note that it's querying all tasks for each project, not just the most recent one. But there's no N+1, and Project#most_recent_task is nicely expressive.

Rails fetch all including last row from association

How about creating an another association like this:

class Scheme < ActiveRecord::Base
  has_one :current_nav, -> { order('id DESC').limit(1) }, class_name: 'Nav'
end

Now you can:

Schema.includes(:current_nav).all

or:

Schema.includes(:current_nav).last(10)

will eager load only last nav of the queried schemes.

Explanation: includes is one of the methods for retrieving objects from database in ActiveRecord. From the doc itself:

Active Record lets you specify in advance all the associations that
are going to be loaded. This is possible by specifying the includes
method of the Model.find call. With includes, Active Record ensures
that all of the specified associations are loaded using the minimum
possible number of queries.

And, since we have the association setup with current_nav, all we had to do is to use it with includes to eager load the data. Please read ActiveRecord querying doc for more information.

How to get last N records with activerecord?

Updated Answer (2020)

You can get last N records simply by using last method:

Record.last(N)

Example:

User.last(5)

Returns 5 users in descending order by their id.

Deprecated (Old Answer)

An active record query like this I think would get you what you want ('Something' is the model name):

Something.find(:all, :order => "id desc", :limit => 5).reverse

edit: As noted in the comments, another way:

result = Something.find(:all, :order => "id desc", :limit => 5)

while !result.empty?
        puts result.pop
end

---

Related Topics