Not Null in "count" or "find" function in rails

Question

I'm trying to find the not null elements in a database

@genus_counts = Table.count(:all, :conditions=> {:col1 => params[:gm], :col2 => nil}, :without => {:col3 => nil})

It's not recognising "without" function. I am in doubt to apply it as array value.

@genus_counts = Table.count(:all, :conditions=> {:col1 => params[:gm], :col3 != nil :col2 => nil})

It's not recognising "!=" operator.... Kindly suggest me and correct above statement. I am in doubt to apply it as array value.

@genus_counts is a local variable, I can't apply it as array. Kindly tell me the suggestion to bring this output to view.erb.html

-- With Regards Palani Kannan. K

Answer 1

as in

http://guides.rubyonrails.org/active_record_querying.html#conditions

you can use something similar to this form instead:

"orders_count = ? AND locked = ?", params[:orders], false

so you can use

"   ... col3 IS NOT null ... "

Answer 2

You should do something like (as answered before)(inside your controller) :

@genus_count = Table.count(:all, :conditions => ['col3 is not null and col2 is null and col1 = ?', params[:gm])

then @genus_count will contain the count you want. Since @genus_count is an instance variable of your controller, you can just refer to it inside your view like this:

The wanted count = <%= @genus_count %>

Hope this helps!

Answer 3

The hash format of specifying conditions such as :conditions=> {:col1 => params[:gm], :col2 => nil} can only be used to specify conditions that are all = joined with AND.

It's nice to read so use that format when applicable, but when you need an OR, or a comparator other than =, you'll need to use a slightly different format:

:conditions => ["col1 = ? AND col3 != ? AND col2 = ?", params[:gm], nil, nil]

This format takes an array, the first element of which is an sql fragment, and the remaining parameters are sql sanitized (preventing sql injection attack, and converting nil to 'NULL' etc) and inserted into the sql fragment replacing the ? (in order of appearance).