CODEWITHSUNDEEP
ruby-on-railsactiverecord
scientiffic
scientiffic

Reputation: 9415

Rails Scope with Multiple Conditions on itself and nested attribute

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

Upvotes: 3

Views: 2315

Answers (2)

charlysisto
charlysisto

Reputation: 3700

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.*)

Upvotes: 0

Yann VERY
Yann VERY

Reputation: 1819

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

Upvotes: 1

Related Questions