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

Question

is it possible to load only the latest associated record of an associated table?

an example:

class author
  attr_accessible :first_name, :last_name, :birthday
  has_many :books
end

class book
  attr_accessible :pages, :date of publication, :title
  belongs_to :author
end

Is there a way to generate a scope to load only the newest released book the author wrote? Or the book with the most pages?

I know, that I could include or join all books. But I don't know if its possible to load only a specific book for each author.

So that I could do a query like this:

Author.authors_and_their_newest_book

So that I could get these results

first_name_author_1, last_name_author_1, birthday_author_1, pages_book_3, date_of_publication_book_3, title_book_3
first_name_author_2, last_name_author_2, birthday_author_2, pages_book_5, date_of_publication_book_5, title_book_5
first_name_author_3, last_name_author_3, birthday_author_3, pages_book_9, date_of_publication_book_9, title_book_9

...

update: I realize that my real problem isn't solved by answering this question.

There is a third model which I have to consider.

class author
  attr_accessible :first_name, :last_name, :birthday
  has_many :books
end

class book
  attr_accessible :pages, :date of publication, :title, _genre_id
  belongs_to :author
  belongs_to :genre
end

class genre
  attr_accessible :name
  has_many :books
end

My real problem is that I want to filter with a scope

scope :with_latest_book_is_a_thriller
scope :with_latest_book_is_a_science_fiction
scope :with_latest_book_is_a_genre_xyz
...

I thought when I have a scope with the latest book for each author I could chain it with another scope for the genre_id. But That leads to a results table where I don't have "authors in case their latest book is genre xyz" but "authors and their latest book from genre xyz".

So for example I have following authors with their books:

author_1: Bruce Wayne  
books:   
title: "how to fight a villain",date_of_publication: March 2010,genre: self-help,  
title: "my life as batman",date_of_publication: April 2012,genre: biography,  
title: "developing high tech equipment", date_of_publication: January 2013, genre: science  

author_2: Clark Kent  
books:  
title: "how I crossed the universe", date_of_publication: January 2009, genre: biography,  
title: "controlling a freezing breath", date_of_publication: February 2012, genre: self-help,  
title: "Clark Kent as Batman", date_of_publication: December 2013, genre: fiction  

author_3: Peter Parker  
books:  
title: "Spider-Man's life", date_of_publication: January 2010, genre: biography,  
title: "how to handle a life with a clone", date_of_publication: February 2011, genre: self-help,  
title: "Spider-Man is becoming a baker", date_of_publication: November 2013, genre: fiction  

I want following results:

scope :with_latest_book_is_a_self_help => empty result  
scope :with_latest_book_is_a_biography => empty result  
scope :with_latest_book_is_a_science => Bruce Wayne, "developing high tech equipment"  
scope :with_latest_book_is_a_fiction => Clark Kent, "Clark Kent as Batman"; Peter Parker, "Spider-Man is becoming a baker" 

But what I get is:

scope :with_latest_book_is_a_self_help => Bruce Wayne, "how to fight a villain"; Clark Kent, "controlling a freezing breath"; Peter Parker, "how to handle a life with a clone"

How can i realize that? Or is it not possible?

Answer 1

You can use a subquery composed via Arel to select the most recent published book for the join, e.g. something like this:

has_one :latest_book, -> { order(published_at: :desc) }, class_name: 'Book'
scope :with_latest_book, -> {
  books = Book.arel_table
  latest_book_publication = books.project(books[:published_at].maximum).where(books[:author_id].eq(arel_table[:id]))
  left_joins(:books).where(books[:published_at].eq(latest_books)).includes(:books)
}

Answer 2

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

Answer 3

I guess this should work

class author
  attr_accessible :first_name, :last_name, :birthday
  has_many :books

  scope :latest, joins(:books).where('books.author_id = authors.id').order('date_of_publication DESC').group('authors.id')
end

Update

For your updated question,you need a scope like this in Book model.

class book
  attr_accessible :pages, :date of publication, :title, _genre_id
  belongs_to :author
  belongs_to :genre

  scope :latest_with_genre, joins(:author,:genre).where("author_id =?","genre_id =?,author.id,genre.id).order('date_of_publication DESC').group('author.id','genre.id')

end

This should work i guess.