Help Converting from SQL to Rails syntax

[John Hinnegan]

Hi all,

I have a table where I'm deleting sparse records -- basically cleaning up broken has_many / belongs_to relationships. (How we get into this situation is a trade-off. We can have '000's of records, and deletes generally happening during peak load, so we just do the clean up later.)

I have ModelA and ModelB. ModelB belongs_to ModelA. ModelA has_many ModelB

Anyway, I have figured out the SQL I want (MySQL:

    DELETE                                                                                                                             

      model_a_table                                                                                                  

    FROM                                                                                                                               

      model_a_table                                                                                                  

      left join model_b_table on model_a_table.model_b_id = model_b_table.id                     

    WHERE                                                                                                                              

      model_b_table.id is NULL;                       

I believe I have the equivalent SELECT figured out as:

ModelA.includes(:model_b).where(:model_b => {:id => nil})

However, I can't seem to get the equivalent delete working. Adding delete_all to the end of the relationship 'breaks' the includes and causes the where to fail.

I'm going to try and figure this out with the old syntax (ModelA.delete(:conditions...)). Thought I'd ask here first as it seems like I should be able to pretty easily convert the SELECT I have to an equivalent delete operation.

Thanks in advance.

[John Hinnegan]

worth noting, since I could have '000's of records, the point of this is to avoid having to load the ids I want to delete into the app and let the DB optimize for it.

[Tim Shaffer]

I am a little confused here. If ModelB belongs to ModelA, I think your foreign key relationship is backwards. The model_b_table should have a foreign key to model_a_id, not the other way around.

Regardless, though, you can pass conditions to delete_all which should help you accomplish what you want.

ModelA.delete_all("model_b_id is null")

[John Hinnegan]

Thanks for trying to decipher my problem. I may have made an error in my description of the problem.

Regardless, after much digging, what I am looking for is not currently possible.

https://github.com/rails/rails/issues/919