Rails Scope to Check If Association Does Not Exist

rails scope to check if association does NOT exist

Rails 4 makes this too easy :)

Foo.where.not(id: Bar.select(:foo_id).uniq)

this outputs the same query as jdoe's answer

SELECT "foos".* 
FROM "foos" 
WHERE "foos"."id" NOT IN (
  SELECT DISTINCT "bars"."foo_id"
  FROM "bars" 
)

And as a scope:

scope :lonely, -> { where.not(id: Bar.select(:item_id).uniq) }

Active Record - Get records where association doesn't exists

The detail may depend on your database, indexes you created, and data you store but I'd recommend giving subqueries a try:

A.where.not(id: B.select(:a_id))

On PostgreSQL this will result in single query like:

SELECT * FROM as WHERE id NOT IN (SELECT a_id FROM bs)

Rails associations NOT EXISTS. Better way?

Items.joins(:organization).includes(:owners).references(:owners).
  where('owners.id IS NULL')

And if you want to use includes for both:

Items.includes(:organization, :owners).references(:organization, :owners).
  where('organisations.id IS NOT NULL AND owners.id IS NULL')

And as @Dario Barrionuevo wrote, it should be belongs_to :organisation in Item.

Using arel_table in the first example:

Items.joins(:organization).includes(:owners).references(:owners).
  where(Owner.arel_table[:id].eq(nil))

In Rails 5 (from comment by @aNoble):

Items.joins(:organization).left_joins(:owners).
  where(Owner.arel_table[:id].eq(nil))

But using includes is still preferable if the relations should be referenced in the code, to avoid extra reads.

Rails active record querying association with 'exists'

You can use includes and then test if the related response(s) exists like this:

def surveys_completed
  members.includes(:responses).where('responses.id IS NOT NULL')
end

Here is an alternative, with joins:

def surveys_completed
  members.joins(:responses)
end

The solution using Rails 4:

def surveys_completed
  members.includes(:responses).where.not(responses: { id: nil })
end

---

Alternative solution using activerecord_where_assoc:
This gem does exactly what is asked here: use EXISTS to to do a condition.
It works with Rails 4.1 to the most recent.

members.where_assoc_exists(:responses)

It can also do much more!

---

Similar questions:

• How to query a model based on attribute of another model which belongs to the first model?
• association named not found perhaps misspelled issue in rails association
• Rails 3, has_one / has_many with lambda condition
• Rails 4 scope to find parents with no children
• Join multiple tables with active records

how to check if has_one exists without loading associated model

You can use: User.first.detail.present?

Check if association exists without incurring a database hit

You could use reflect_on_all_associations as:

Foo.reflect_on_all_associations(:belongs_to).map(&:name).include?(:example_assoc)

Where :example_assoc is one of the belongs_to association.

Or if you have an instance of model class:

@foo.class.reflect_on_all_associations(:belongs_to).map(&:name).include?(:example_assoc)

Rails - scope for records that are not in a join table alongside a specific association

What you need to do is:

1. Make a left join with the reference table, with an additional condition on the tournament ID matching the one that you want to find the not-selected players for
2. Apply a WHERE clause indicating that there was no JOIN made.

This code should do it:

# player.rb
scope :not_selected, -> (tournament) do 
  joins("LEFT JOIN tournament_players tp ON players.id = tp.player_id AND tp.tournament_id = #{tournament.id}").where(tp: {tournament_id: nil})
end

If only Rails had a nicer way to write LEFT JOIN queries with additional conditions...

A few notes:

1. Don't join the actual relation (i.e. Tournament), it dramatically decreases performance of your query, and it's unnecessary, because all your condition prerequisites are inside the reference table. Besides, all the rows you're interested in return NULL data from the tournaments table.
2. Don't use eager_load. Besides to my best knowledge its not supporting custom conditions, it would create models for all related objects, which you don't need.

---

Related Topics