return 0 if null count

source: has_many comments

comments: has_many training_comments

@negative_comments = Source.joins(:comments => [:training_comments]).where("training_comments.category_id = ? and comments.spam = ?", 2, false).select("sources.*, count(comments.id) as ncount").group("comments.source_id")

I want to list sources with their negative comments count but i am loosing sources which don't have negative_comments(training_entries.category_id = 2). I tried everything to achieve this. I tried left join, i tried ifnull but none of them worked. Any help will be really really appreciated..

What i'm trying to do

Sources 开发者_如何学JAVA Count

source1 5

source2 0

source3 13

what i'm getting is

Sources Count

source1 5

source3 13

Sources table

id: integer
name: string

Comments table

id: integer
source_id: integer
spam: boolean

Training_comments table

id: integer
comment_id: integer
category_id: integer

You're getting a zero because the standard JOIN doesn't produce anything when there is no match. You need to get a LEFT OUTER JOIN past ActiveRecord; something like this:

joins('LEFT OUTER JOIN comments ON comments.source_id = sources.id')

AFAIK, you have to drop down to SQL to get a LEFT OUTER JOIN.