Has many through query using the date

Question

I am trying to use the value from a has_many :through relationship to retrieve a list of Authors but my logic is failing.

What would be the best way to achieve this

Currently trying this without success

start_date = '01/01/0001'.to_date.beginning_of_day
end_date = '01/01/2020'.to_date.end_of_day

Author.includes(:books).where("date(books.release_date) BETWEEN ? AND ?", start_date, end_date)

I can retrieve just the books by using

Book.where("date(release_date) BETWEEN ? AND ?", start_date, end_date)

But want to get this query above working

Answer 1

Your query is likely failing because you are using includes with a SQL string fragment. Rails does not infer any associations on includes using SQL strings, so references must be used in conjunction:

Author.includes(:books).references(:books).where("date(books.release_date) BETWEEN ? AND ?", start_date, end_date)

Alternatively, you can use the query DSL to construct the same where clause and Rails will be able to infer the association:

Author.includes(:books).where({ books: { release_date: 1.year.ago..Time.now } })

If you're just trying to filter down to a list of authors and don't need the books at all, I'd recommend a subquery:

author_ids = Book.where("date(release_date) BETWEEN ? AND ?", start_date, end_date).select(:author_id)
Author.where(id: author_ids)