Rails scope filtering elements with no has_many associated elements

Question

I have the following models:

class Property < ActiveRecord::Base
    has_many :photos
    scope :no_photos, -> { where('properties.id NOT IN (SELECT DISTINCT(property_id) FROM photos)') }
end

class Photo < ActiveRecord::Base
    belongs_to :property
end

I know that my scope is really inefficient. I need another way to get the properties that don´t have any photos associated to them.

Any help?

Related: https://stackoverflow.com/q/5319400/12484 – Jon Schneider Jun 07 '19 at 15:07 — Jon Schneider, Jun 07 '19 at 15:07

MrYoshiji · Accepted Answer · 2017-12-21T14:40:34.427

9

You can do the following:

class Property < ActiveRecord::Base
  has_many :photos
  scope :has_no_photo, includes(:photos).where(photos: { id: nil })
  scope :has_photo, includes(:photos).where('photos.id IS NOT NULL')
  # rails 4 or higher (thanks to @trip)
  scope :has_photo, includes(:photos).where.not(photos: { id: nil })

Similar questions:

edited Dec 21 '17 at 14:40

answered Mar 09 '15 at 14:16

MrYoshiji

54,334
13
124
117

Thanks, how would the opposite work? scope :has_photo ? – Sebastián Jara Mar 09 '15 at 14:21
I added the scope `has_photo` to my answer, hope this helps! – MrYoshiji Mar 09 '15 at 14:23
also `scope :has_photo, joins(:photos).where.not(photos: {id: nil})` – Trip Dec 21 '17 at 13:12
1

@Trip I added your suggestion to my answer, thanks for keeping that post up-to-date :) – MrYoshiji Dec 21 '17 at 14:41

Rails scope filtering elements with no has_many associated elements

1 Answers1