CODEWITHSUNDEEP
sqlruby-on-railsruby
user3124172
user3124172

Reputation: 23

Rails SQL Query counts

I have a table called "Books" that has_many "Chapters" and I would like to get all Books that have have more than 10 chapters. How do I do this in a single query?

I have this so far...

Books.joins('LEFT JOIN chapters ON chapters.book_id = books.id')

Upvotes: 2

Views: 94

Answers (3)

Carlos Ramirez III
Carlos Ramirez III

Reputation: 7434

Here is the query using Rails 4, ActiveRecord

Book.includes(:chapters).references(:chapters).group('books.id').having('count(chapters.id) > 10')

Upvotes: 1

user3124172
user3124172

Reputation: 23

Found the solution...

query = <<-SQL
  UPDATE books AS b
  INNER JOIN
  (
      SELECT books.id
      FROM books
      JOIN chapters ON chapters.book_id = books.id
      GROUP BY books.id
      HAVING count(chapters.id) > 10
  ) i
  ON b.id = i.id
  SET long_book = true;
SQL

ActiveRecord::Base.connection.execute(query)

Upvotes: 0

Daniel Williams
Daniel Williams

Reputation: 8895

I don't know the rails very well but the SQL would be like this:

SELECT b.* FROM Books b 
JOIN Chapters c ON b.id = c.book_id 
GROUP BY b.id
HAVING COUNT(*) > 10

According to the ActiveRecord docs it would then be 

Books.joins(:chapters)
  .group('books.id')
  .having('count() > 10')

UPDATE: I figured it out. Here what I did in rails if it helps anyone

query = <<-SQL
  UPDATE books AS b
  INNER JOIN
  (
      SELECT books.id
      FROM books
      JOIN chapters ON chapters.book_id = books.id
      GROUP BY books.id
      HAVING count(chapters.id) > 10
  ) i
  ON b.id = i.id
  SET long_book = true;
SQL

ActiveRecord::Base.connection.execute(query)

Upvotes: 0

Related Questions