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')
Add a comment
|
3 Answers
Here is the query using Rails 4, ActiveRecord
Book.includes(:chapters).references(:chapters).group('books.id').having('count(chapters.id) > 10')
Comments
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)
5 Comments
user3124172
This doesn't work for me because I can't do anything with the data. It needs a select or where clause somewhere, because when I do this: Books.joins(:chapters) .group('books.id') .having('count() > 10') .update_all(long_book: true) It updates ALL the books instead of the ones that are selected through the group by
Ben
Are you sure?
update_all purports to respect conditions. Can you paste the generated SQL?user3124172
So after a bit of research, can't use group by in an update statement. This is what I get: UPDATE
books JOIN chapters ON chapters.book_id = books.id SET long_book = true ... notice that it leaves out the group by and having
Daniel Williams
Can you make it work in two passes? Force the group and having to evaluate then run the update? Essentially you want to receive all book ids then update all those books in a batch process.
user3124172
So I got this far: UPDATE books b SET long_book = true WHERE b.id IN ( SELECT books.id FROM books JOIN chapters ON chapters.book_id = books.id GROUP BY books.id HAVING count(chapters.id) > 10 ); but I get this result: ERROR 1093 (HY000): You can't specify target table 'b' for update in FROM clause
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)
Comments
