Finding Nil Has_One Associations in Where Query

Finding nil has_one associations in where query

It's quite common task, SQL OUTER JOIN usually works fine for it. Take a look here, for example.

In you case try to use something like

not_purchased_items = Item.joins("LEFT OUTER JOIN purchases ON purchases.item_id = items.id").where("purchases.id IS null")

Rails: Query nil has_one association

The problem is, that

patient.code_status

is not a column, but a method, added by Rails when you say

class Patient
  has_one :code_status
end

Here is how you'd get all patients not associated with any code status:

Patient.includes(:code_status).where(code_statuses: { id: nil })

How to find where has_one :through association is not nil in Rails

Install and use where_exist gem

gem 'where_exists'

User.where_exists(:profile_image)

ActiveRecord - where query based on has_one association

I think an inner join will give you what you are looking for. Something like following:

Class1.joins(:class2)

has_one relation automatically set nil if more than one

By default, the has_one association executes a nullify. Adding the dependent: :destroy solved the problem.

class Shop
  has_one :shop_info, dependent: :destroy
end

Just if someone wants more info, the ActiveRecord code for has_one replacement record is this:

https://github.com/rails/rails/blob/v4.2.6/activerecord/lib/active_record/associations/has_one_association.rb#L24-L51

BUT if you add a dependent option in the association, executes the delete method as well:

https://github.com/rails/rails/blob/v4.2.6/activerecord/lib/active_record/associations/has_one_association.rb#L7-L22

Rails: Finding models with missing has_one records

As @tadman & @antonk already said, you probably want to use scope:

scope :without_profile, where(profile_id: nil) 

EDIT:
To answer @David Mauricio's question: you could use it by calling User.without_profile, to return the AR association of all users with a nil :profile_id.

@DriverDan : then I'm really unsure what you're asking for. Ask another question with more details, and we can try to answer it!

How to improve has_one association activerecord queries

biography = Biography.where(user_id: current_user.id).eager_load(:lifestyle, :education, :location).first


:)

Grails GORM query for null hasOne association fails

I finally solved it, thanks to the comment of @Koloritnij and the modified answer of @Alexander Suraphel.
Thanks for that.

If the foreign key is on the B table (due to the hasOne), the following two queries solve the case:

finding all As with Bs: (b is not null):

A.withCriteria {
  b {}
}

This results in an inner join: SELECT * FROM a INNER JOIN b ON a.id=b.a_id;

finding all As without Bs (b is null):

A.withCriteria {
  createAlias('b', 'bAlias', CriteriaSpecification.LEFT_JOIN)
  isNull 'bAlias.id'
}

This results in a left outer join: SELECT * FROM a LEFT OUTER JOIN b ON a.id=b.a_id WHERE b.id IS NULL;

---

Related Topics