Rails Scope with Multiple Conditions on itself and nested attribute

Question

In my Rails app, the class Project is defined as follows:

has_many :spins
has_one :video

I'd like to create a scope for Project that returns all projects that meet the following conditions:

project.video.present? || project.spins.count > 0 && project.spins.first.default_video.present?

I've read about how to use multiple conditions in a scope, but I'm unsure about how to combine this with a conditional on a nested attribute (in this case, spins).

How do I create a scope for Project that meets these conditions?

Edit: For clarification, default_video is a class method for Spin:

def default_video
    self.videos.where("has_audio IS NULL").first
end

I can help you a little bit for the first two conditions. For the first one, don't check present, but `WHERE videos.project_id (or projects.video_id) not NULL`. The second one is `WHERE COUNT('spins.id') > 0`. I don't know about the last one, since I don't really understand the relationship that is going on and don't know if you can use the .first part. — bo-oz, Nov 24 '15 at 06:35
what's the default_video in spin : a column value, an association ? Do you have eventually a Spin#default_video_id ? — charlysisto, Nov 24 '15 at 11:39
do videos have a spin_id column ? it's difficult to come up with a solution without a clear schema of your tables... — charlysisto, Nov 25 '15 at 03:46

Yann VERY · Answer 1 · 2015-11-24T12:17:54.293

As I know ActiveRecord does not provide the feature merging scopes with OR ( for the moment). I think there is two options to deal with this feature :

1 - Create a scope that returns projects containing a video or with a first spin containing a default video :

scope :with_video, -> { joins(
  'LEFT JOIN spins 
    ON spins.id = (
      select p.id
      from spins as p WHERE projects.id = p.project_id ORDER BY p.id LIMIT 1
    )
   JOIN videos on videos.project_id = projects.id 
   WHERE 
     spins.default_video IS NOT NULL 
   OR 
     videos.project_id IS NOT NULL
')

}

I think this solution is unreadable and it's difficult to maintain. I do not make some performance tests with this query, perhaps this one must be improved

2 - create 2 scopes and merge both results

scope :with_one_video, -> { joins(:videos) }
scope :with_spin_video, -> { joins('JOIN spins ON 
  spins.id = ( 
    SELECT S.id
    FROM spins as S 
    WHERE projects.id = S.project_id 
    ORDER BY S.id LIMIT 1
  )').where('spins.default_video IS NOT NULL')}

def with_video
  (with_one_video.to_a + with_spin_video.to_a).uniq
end

This one is better with reusable scopes but the second scope is still difficult to read. With this solution results has been return as an Array, it can be a disadvantage depending on the situation.

Edit: I'm interrested by all answers with differents solutions, improvments on sql queries or even replaced them by activerecord / arel

Isn't the where(not null) clause unnecessary since rails joins are inner joins? — charlysisto, Nov 24 '15 at 12:10
Exact ! on with_one_video scope the clause where is not necessary. Thanks — Yann VERY, Nov 24 '15 at 12:16

charlysisto · Answer 2 · 2015-11-25T10:29:17.890

The first condition is fairly easy since rails joins are inner Joins so it excludes non existant associations :

Project.joins(:video)

The second condition could be written this way (provided default_video is column of spins table) :

Project.joins(:spins).
        group("spins.project_id").
        having("count(spins.default_video) > 0")

Unfortunately they don't work well together.

Instead I propose 2 left outer joins (keeping all projects) and then group by project.id. That'll "compress" all the joins in one row and from there we check for nulls by counting some of the joined columns.

 Project.joins("left join videos on videos.project_id = project.id").
         joins("left join spins on spins.project_id = project.id").
         group("projects.id").
         having("count(videos.project_id) > 0 OR count(spins.default_video) > 0")

Edit : Based on additional informations we need one more join : the one tying videos to spin (as for the default_video which is an association).

 Project.joins("left join videos on videos.project_id = project.id").
         joins("left join spins on spins.project_id = project.id").
         joins("inner join videos s_vid on s_vid.spin_id = spins.id").
         group("projects.id").
         having("count(videos.project_id) > 0 OR count(s_vid.spin_id) > 0").
         select("projects.*)

Rails Scope with Multiple Conditions on itself and nested attribute

2 Answers2