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?

Will try it in in a few minutes :) – coderuby May 30 '14 at 08:47 — coderuby, May 30 '14 at 08:47

coderuby · Accepted Answer · 2018-11-30T09:23:13.897

10

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

edited Nov 30 '18 at 09:23

answered May 30 '14 at 12:53

coderuby

1,188
1
11
26

1

Thank you for this answer! For rails 5.2 it causes `NoMethodError: undefined method 'expr' for nil:NilClass`. See https://stackoverflow.com/questions/57074698/how-are-joins-in-scopes-in-rails-5-2-different-from-rails-5-1 for the details. Any thoughts? – Hirurg103 Aug 19 '19 at 13:01

Pavan · Answer 2 · 2014-05-30T11:38:03.593

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.

edited May 30 '14 at 11:38

answered May 29 '14 at 18:24

Pavan

33,316
7
50
76

This only gives me one author with his newest book, but not all authors with their newest book. I think in my question it was not clear, that I wanted all authors. So I added some more details. – coderuby May 30 '14 at 09:21
@railsnewbie Try my updated answer! I just removed `limit(1)`. – Pavan May 30 '14 at 09:24
I tried that too, that gives me all authors with all books in order. But I only wanted the newest one. One entry per author in my results. 1. Author.joins(:books)..count => 1204 2. Author.joins(:books).order('date_of_publication Desc').count => 1204 – coderuby May 30 '14 at 09:25
@railsnewbie Try giving just `Author.latest` – Pavan May 30 '14 at 09:29
gives me an NoMethodError: undefined method `latest' for # Do you mean last? With last I only get the latest author with no join options. – coderuby May 30 '14 at 09:32
No! I mean the `latest` only.Do you defined the `scope` in the `Author` model? – Pavan May 30 '14 at 09:34
oh, sorry. Now I know what you mean. No I just tried the query in the console. Will try it with the scope now. – coderuby May 30 '14 at 09:35
Author.latest.count => 1204. Still an entry for each book the authors had written. – coderuby May 30 '14 at 09:38
try like this `Author.joins(:books).where('books.author_id => author.id).order('date_of_publication DESC')` – Pavan May 30 '14 at 09:44
I had to adjust the where clause a little bit. Author.joins(:books).where('books.author_id = authors.id).order('date_of_publication DESC'). But Author.latest.count is still 1204. – coderuby May 30 '14 at 09:57
That works. But I realize that this doesn't solves my initial problem. I will update my question. – coderuby May 30 '14 at 10:33
@railsnewbie Initial problem? What is that? – Pavan May 30 '14 at 10:37
I updated my question. So I hope it is clear now what I mean with my "initial problem" – coderuby May 30 '14 at 11:16
Why do you put the author_id in the where clause? I want to use it to get all authors with a latest book from genre xyz. So I changed your solution to `scope :latest_with_genre, joins(:author,:genre).where("genre_id = 1").order('date_of_publication DESC').group('books.author_id','books.genre_id')`. But this gives me the previous result where I have "authors and their latest book from genre xyz". – coderuby May 30 '14 at 12:08
@railsnewbie Give `genre_id = genre.id` and check. – Pavan May 30 '14 at 12:11
When I change it to `scope :latest_with_genre, joins(:author,:genre).where("genre_id = genre.id").order('date_of_publication DESC').group('books.author_id','books.genre_id')` I get nearly all books. And how do i specify the genre? Not sure how do you mean it. – coderuby May 30 '14 at 12:17
I think I have the answer. I will post it now – coderuby May 30 '14 at 12:30

score 1 · Answer 3 · answered Jan 20 '19 at 04:27

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)
}

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

3 Answers3