1

I have a many to many relationship through a has_many through

class Person < ActiveRecord::Base
  has_many :rentals
  has_many :books, through rentals
end

class Rentals < ActiveRecord::Base
  belongs_to :book
  belongs_to :person
end

class Book < ActiveRecord::Base
  has_many :rentals
  has_many :persons, through rentals
end

How can I get the persons that have only one book?

Petran
  • 7,677
  • 22
  • 65
  • 104

2 Answers2

2

If the table for Person is called persons, you can build an appropriate SQL query using ActiveRecord's query DSL:

people_with_book_ids = Person.joins(:books)
                             .select('persons.id')
                             .group('persons.id')
                             .having('COUNT(books.id) = 1')
Person.where(id: people_with_book_ids)

Although it's two lines of Rails code, ActiveRecord will combine it into a single call to the database. If you run it in a Rails console, you may see a SQL statement that looks something like:

SELECT "persons".* FROM "persons" WHERE "deals"."id" IN 
(SELECT persons.id FROM "persons" INNER JOIN "rentals" 
ON "rentals"."person_id" = "persons"."id"
INNER JOIN "books" ON "rentals"."book_id" = "books"."id" 
GROUP BY persons.id HAVING count(books.id) > 1)
ScottM
  • 7,108
  • 1
  • 25
  • 42
  • 2
    I believe Rails will actually group those two into a single SQL query using a sub-query. If this is a highly trafficked area of the codebase it would be worthwhile to replace the strings with more flexible scopes (potentially leveraging Arel for the counts and column names). This ultimately allows for something like `books_that_are_the_only_rental = Book.where(persons: Person.with_one_book)`. – coreyward Dec 19 '18 at 16:15
  • You may well be right! I actually *meant* to add a `.map(&:id)` to the end of `people_with_book_ids`, which would force the operation into two queries (at the risk of hitting scale issues if you have loads of people in the DB). Omitting it may be a happy accident :) – ScottM Dec 19 '18 at 16:18
  • Yeah, the subquery only works if the subselect returns a single field: corrected my code and the explanation. Thanks, @coreyward! – ScottM Dec 19 '18 at 16:25
  • Notes: the plural of `person` would be `people` (active_support will handle this), you have "deals" in the query not sure why, and the having clause in your first part does not match the second one. – engineersmnky Dec 19 '18 at 18:38
  • Why not directly `.select('persons.*')` so that you don't need a second statement for `Person.where(id: people_with_book_ids)`? – MrYoshiji Dec 19 '18 at 19:01
0

If this is something you want to do often, Rails offers what is called a counter cache:

The :counter_cache option can be used to make finding the number of belonging objects more efficient.

With this declaration, Rails will keep the cache value up to date, and then return that value in response to the size method.

Effectively this places a new attribute on your Person called books_count that will allow you to quite simply filter by the number of associated books:

Person.where(books_count: 1)
Community
  • 1
  • 1
coreyward
  • 77,547
  • 20
  • 137
  • 166
  • Yeah, if this is the sort of query which is going to be used a lot (and with a different `books_count`) a counter cache can be useful! There are a couple of gotchas to using `counter_cache` with `has_many :through` though – [this SO answer](https://stackoverflow.com/a/9189982/1326518) has some useful pointers. – ScottM Dec 19 '18 at 16:14