How do I use the hash :condition on a .find for a nil? method in Rails (has_one relationship)

Question

I want to find all Contacts where the value of their :has_one relationship to :contact_status does not have a value (does that mean nil?)

I created an alias_attribute :status, :status_contact

Can I do something along the lines of:

contacts = Contact.find(:all, :conditions => {:contact_status => nil }, :include => {:status_contact} )

I'm not sure exactly how nil works. Basically, I don't assign any value to it, it is just a :has_one relationship.

EDIT: Sounds like contacts should be a belongs_to relationship so making change From the comments, it sounds like Contacts should actually have a belongs_to relationship to status_contacts which in turn 'has_one' with Contacts.

I am using Searchlogic to create named_scopes...I think it solves the problem, anyone familiar with that?

However, there are times the user can assign a value to that. I don't want those to come up in the find.

Currently get this error:

SQLite3::SQLException: near ".": syntax error: SELECT "contacts"."id" AS t0_r0, "contacts"."first_name" AS t0_r1, "contacts"."last_name" AS t0_r2, "contacts"."title" AS t0_r3, "contacts"."phone" AS t0_r4, "contacts "."fax" AS t0_r5, "contacts"."email" AS t0_r6, "contacts"."company" AS t0_r7, "contacts"."created_at" AS t0 _r8, "contacts"."updated_at" AS t0_r9, "contacts"."campaign_id" AS t0_r10, "contacts"."date_entered" AS t0_ r11, "contacts"."company_id" AS t0_r12, "contacts"."address_id" AS t0_r13, "contacts"."vcard_file_name" AS t0_r14, "contacts"."vcard_content_type" AS t0_r15, "contacts"."vcard_file_size" AS t0_r16, "contacts"."vcar d_updated_at" AS t0_r17, "contacts"."direct" AS t0_r18, "contacts"."sugarcrm" AS t0_r19, "status_contacts". "id" AS t1_r0, "status_contacts"."status" AS t1_r1, "status_contacts"."contact_id" AS t1_r2, "status_contac ts"."created_at" AS t1_r3, "status_contacts"."updated_at" AS t1_r4 FROM "contacts" LEFT OUTER JOIN "status _contacts" ON status_contacts.contact_id = contacts.id WHERE (:status_contact.status = NULL)

Answer 1

try

contacts = Contact.find(:all, :conditions => "status_contact_id IS NULL")

assuming your fk column is contacts.status_contact_id

UPDATE: nathanvda is correct; Also, I think you can do it without find_by_sql - untested...

contacts = Contact.find(:all, 
    :conditions => "contacts.id NOT IN (select contact_id from status_contacts)"   
)

NOTE: I still think you really want to use Contact with belongs_to :status_contact

Answer 2

Depends on the version of Rails, I mostly work in 2.3.5, but I believe "conditions", in this context, is meant to be a "SQL-like fragment."

Typically you'd do something like

contacts = Contact.find(:all, :include => :contact_status, :conditions => ["contact_status.status = ?", nil])

Also, nil in Ruby means completely devoid of value. It stores nothing. [], an empty array, will return false if you send it the message nil?, it will respond true to the message empty? however.

Answer 3

Your error doesn't fit the code you are showing. I guess you are doing something like

:conditions => ['status = ?', nil]

but what you should be checking is not the status, which is the linked object. It is how you would check it in ruby-code, but not in sql.

Inside your table, you would have a field called status_contact_id, and when that field is NULL (the database equivalent of nil) then it has no relation.

So you would indeed need to do something like

contacts = Contact.find(:all, :conditions => "status_contact_id IS NULL")

[EDIT] Since you are using a has_one relation, totally read over that, the foreign key is indeed defined in the status_contacts table. But, to query the items not having any status_contacts gets a bit more complicated.

In sql, you would do something like

select * from contacts where id not in (select contact_id from status_contacts)

and I would translate that to ruby like this:

contacts = Contacts.find_by_sql("select * from contacts where id not in (select contact_id from status_contacts)"

Personally i can't think of a better way right now.