Rails 3 finding parents which have no child

Question

In a one to many relationship with no counter cache how can I find parents with no child?

user.rb

has_many :pages

page.rb

belongs_to :user

I've tried

User.includes(:pages).where("pages.user_id is NULL")

This is making trouble in MySQL.

score 15 · Accepted Answer · answered Jul 03 '11 at 22:31

15

Try

User.joins("left join pages on pages.user_id = users.id").where("pages.user_id is null")

answered Jul 03 '11 at 22:31

Omar Qureshi

score 0 · Answer 2 · answered Jan 27 '13 at 03:36

0

I believe something like

 User.all(:joins => :comments, :select => "users.*, count(comments.id) as comments_count", :group => "users.id")

might work also...

answered Jan 27 '13 at 03:36

rogerdpack

score 0 · Answer 3 · answered Jul 03 '11 at 22:31

0

One way would be

User.where("(SELECT COUNT(*) FROM pages WHERE pages.user_id = users.id) = 0")

But I'm not sure how (in)efficient that would be.

answered Jul 03 '11 at 22:31

Dogbert

2

count(*) is not something that i'd personally want to run if it was a common query. – Omar Qureshi Jul 03 '11 at 22:32

3 Answers3