2

I have a model called Facility. It has_many :addresses. The Address model has_one :city.

Now I want to run a condition:

  • to get all facilities that do not have associated address;
  • if they do have addresses check if they do not have city model associated with that addresses.

I have tried the first condition but I am unable to combine an OR for it.

This gets all facilities that do not have an address model associated to it

Facility.includes(:addresses).where( :addresses => {:facility_id => nil})

Some error tries are: 

Facility.includes(:addresses).where( :addresses => ({:facility_id => nil}).or({:city_id => nil}) );
Facility.includes(:addresses).where( :addresses => ({:facility_id => nil}).or(:address => {:city_id => nil}) )
potashin
  • 44,205
  • 11
  • 83
  • 107
coderVishal
  • 8,369
  • 3
  • 35
  • 56
  • Did you try `.or({:addresses => {:city_id => nil}}) );`? – dan-klasson Jan 11 '16 at 09:28
  • yes i did, does not work i made a edit, check if that was what you where saying, the error is _NoMethodError: undefined method `or' for {:facility_id=>nil}:Hash_ – coderVishal Jan 11 '16 at 09:36
  • 1
    @coderVishal, you may be interested in using Squeel. For OR conditions, and other matchers such as < or >, Squeel makes the code clean instead of using SQL strings that are sensitive to error. – Jay-Ar Polidario Jan 11 '16 at 10:02

3 Answers3

2

Try the following:

Facility.includes(:addresses)
        .where('addresses.facility_id is null or addresses.city_id is null')
        .references(:addresses)

You can also find interesting this post, concerning possible implementations of the or condition in the activerecord queries.

Community
  • 1
  • 1
potashin
  • 44,205
  • 11
  • 83
  • 107
  • Thanks this is working, may i know the purpose of adding references, since without it I am getting an error. – coderVishal Jan 11 '16 at 09:52
  • 1
    it's necessary since there is sql in where clause instead of hash http://guides.rubyonrails.org/active_record_querying.html#specifying-conditions-on-eager-loaded-associations – user3409950 Jan 11 '16 at 09:54
  • 1
    @coderVishal: you check columns in the `where` clause from another table, `addresses`, so since 4.2 you should explicitly `reference` them. – potashin Jan 11 '16 at 09:54
  • @potashin thank you a lot. Saved another day. I would like to learn this related stuff, can you point me in a good direction(some tutorial or link) – coderVishal Jan 11 '16 at 09:56
  • 1
    @coderVishal: well, official docs are [pretty explicit on the issue and related stuff](http://guides.rubyonrails.org/active_record_querying.html#specifying-conditions-on-eager-loaded-associations) – potashin Jan 11 '16 at 09:58
0

Try this

Facility.includes(addresses: :city).where("addresses.facility_id IS NULL OR addresses.city_id IS NULL");

Hope this would be helpful.

Nitin Srivastava
  • 1,414
  • 7
  • 12
0

First, using includes when you want to do conditions on associations can have negative side effects:

  • You will eager load data, which, if not needed, means doing more work for nothing
  • The addresses that are eager loaded are only those matching the condition. So if you thn use addresses, you don't get all of them for that record (only those that matched the condition). This causes weird bug or hard to understand working code.

To avoid this and all other issues, I recommend to use a gem I made specifically for this: activerecord_where_assoc

Your problem can be simplified, I will do so after replying to your request as-is:

You seem to be using Rails 4.2 according to the tags. So you don't yet have access to the #or method yet. So this is how you could do it:

sql_no_address = Facility.assoc_not_exists_sql(:addresses)
sql_no_city = Facility.assoc_exists_sql(:addresses) { where_assoc_not_exists(:city) }
Facility.where("#{sql_no_address} OR #{sql_no_city}")

If you have Rails 5 or more:

Facility.where_assoc_not_exists(:addresses).or(Facility.where_assoc_not_exists([:addresses, :city]))

Now, a simpler solution. Notice that if a facility has no addresses, then it cannot have a city, because it must go through an address to get to a city. Really, your problem is just "I want Facilities that have no cities". This is what passing an array to the methods do, it tries to go from one to the other until the end.

Facility.where_assoc_not_exists([:addresses, :city])

Here are the introduction and examples. Read more details in the documentation.

Maxime Lapointe
  • 93
  • 4