查询：的has_many，所有的子域是零 [英] query: has_many where all the child fields are nil

问题描述

我用Rails 3.2通过ActiveRecord和MySQL，我有一个模型，许多关联关系：

I'm using Rails 3.2 with ActiveRecord and MySQL and I have models with one to many association:

class Author
  has_many :books
end

class Book
  belongs_to :author
  attr_accessible :review
end

我想找到有所有的没有复习用书的作者。我想：

I want to find authors that have all the books without review. I tried:

Author.includes(:books).where('book.review IS NIL')

但显然没有奏效，因为它发现有没有审查至少有一个书的作者。我应该使用什么样的查询？

but is obviously didn't work, because it finds authors that have at least one book without review. What query should I use?

推荐答案

SQL很简单：

SELECT authors.name, count(books.review is not null) 
FROM authors LEFT JOIN books ON (authors.id=books.author_id) 
GROUP BY authors.name
HAVING count(books.review) == 0

其转换到AR查询语言可以带我一段时间...... 好了，这似乎是这样的：

Translating it to the AR query language may take me some time... OK, so it seems to look like this:

Author.count('books.review', joins: :books, select: 'name', 
              group:'name', having: 'count_books_review=0')

至于我的SQL看起来不太奇怪那么这一点; - ）

As for me SQL looks much less weird then this ;-)

这篇关于查询：的has_many，所有的子域是零的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！