Rails find all records except already second level associated

Question

I have the following models in my rails application

Model community:

class Community < ActiveRecord::Base
  has_many :community_program
end

Model community_program:

class CommunityProgram < ActiveRecord::Base
  belongs_to :program_name
end

Model program_name:

class ProgramName < ActiveRecord::Base
  has_many :community_programs
end

I am trying to find a way to pull all the program_names which don't belong to a community_program which has already been assigned to a community.

I have been able to pull all program_names and then remove the program_names which are already associated with a community but that is very expensive. Can I directly pull the second level unassociated records?

EDIT: The question is different from the linked question since in this case, program_name might have an association with a Community Program for a different community but we still want to include that name if it doesn't have an association with the current community.

**EDIT 2: I want to extract all the Program Names which haven’t yet been assigned to a Community Program associated with a particular Community. They can be associated with the Community Programs associated with a different community. The use case is that a user should be able to select a name from the list of remaining names and assign that to a community program associated with their community.

Program names are global and although the program name for community programs of a particular community are unique but different communities can have community programs associated to the same program name**

Possible duplicate of [Find all records which have a count of an association greater than zero](https://stackoverflow.com/questions/20183710/find-all-records-which-have-a-count-of-an-association-greater-than-zero) — Josh Brody, Jul 31 '18 at 21:56
@JoshBrody I don’t think it’s a duplicate because they can have other associations I just want to ensure that they are not associated to the specific community through a community program but the same name can be connected to different community — anonn023432, Jul 31 '18 at 22:01
Can you rephrase what you need to do? I understand this: You need all `ProgramName` that are 1) NOT associated with any `CommunityProgram` and 2) Already associated with a `Community` ?? — gasc, Aug 01 '18 at 03:07
By the way, I think you're missing an association in your `CommunityProgram` model , a `belongs_to :community` — gasc, Aug 01 '18 at 03:14
@gasc yes you are correct there is a missing association. I am using Stackoverflow on mobile so I can’t edit it but the association should be ther — anonn023432, Aug 01 '18 at 03:25

Pavel Mikhailyuk · Accepted Answer · 2018-08-07T13:02:17.837

1

Let except_community is a particular Community which associations should be excluded. Code below will produce one SQL query:

except_relation = except_community.
  community_programs.
  select(:program_name_id)

ProgramName.
  where.not(id: except_relation)

You can add it as an instance method of Community model.

edited Aug 07 '18 at 13:02

answered Aug 01 '18 at 13:36

Pavel Mikhailyuk

2,757
9
17

If possible can you please explain why `select` is better than `pluck` in this situation even though `pluck` is supposed to be more efficient in general? – anonn023432 Aug 07 '18 at 18:33
1

`pluck` is not supposed to be more efficient in general :) The `select` is not `Enumerable#select` but chainable method of `ActiveRecord::Relation` like `where`, `order` etc. So, `except_relation = ...` does not produce real SQL query(like `pluck`) but just builds instance of `ActiveRecord::Relation`. And so last lines of code look like `Model.where.not(id: ar_relation)` which produces one SQL query like `SELECT * FROM program_names WHERE id NOT IN(SELECT program_name_id FROM ...)` – Pavel Mikhailyuk Aug 07 '18 at 19:36

gasc · Answer 2 · 2018-08-01T20:01:56.077

Maybe something like:

class Community < ActiveRecord::Base
  has_many :community_programs
end

class CommunityProgram < ActiveRecord::Base
  belongs_to :program_name, optional: true
  belongs_to :community
end

class ProgramName < ActiveRecord::Base
  has_many :community_programs
end

some_community            = Community.find some_community_id
programs_from_a_community = some_community.community_programs.map &:id 
program_names_to_exclude  = CommunityProgram.where(id: programs_from_a_community)
                                            .where.not(program_name_id: nil)
                                            .map { |c_p| c_p.program_name.id }
program_names             = ProgramName.where.not(id: program_names_to_exclude)

anonn023432 · Answer 3 · 2018-08-07T19:44:55.753

0

My edition for the answer I accepted earlier was rejected so I am adding it as another answer. The solution provided by Pavel Mikhailyuk fixed my issue.

Since I wanted to find the most efficient query I could so I ended up updating it and using:

ProgramName.where.not(id: except_community.community_programs.pluck(:program_name_id))

EDIT: The selected answer is the more efficient than this.

edited Aug 07 '18 at 19:44

answered Aug 06 '18 at 15:26

anonn023432

2,940
6
31
63

Thank you for edit proposal, I've updated my answer with grammar fixes. – Pavel Mikhailyuk Aug 07 '18 at 13:03
1

You are wrong with your assumption: `select` in my answer does NOT build any ActiveRecord model instance, just ActiveRecord relation. My example produces only ONE sql query with SUB-select, when `pluck` one produces TWO sql queries and depends on `program_name_id` count. So, my answer "is more efficient" than `pluck` one :) – Pavel Mikhailyuk Aug 07 '18 at 13:11
@PavelMikhailyuk Yes, you are correct. My reasoning was flawed over here and running a `to_sql` followed with benchmarking clearly showed that `select` is more efficient in this case. – anonn023432 Aug 07 '18 at 18:32

Brittany Hicks · Answer 4 · 2018-07-31T22:17:07.573

-1

You could do this a few different ways, depending on which controller/view you are displaying them on.

Create an association on the Community model:

has_many :unassigned_program_names, -> { where community_program_id: nil }

Create a scope like this on the ProgramName model:

scope :unassigned, -> { where(community_program_id: nil) }

Hope this helps. :)

edited Jul 31 '18 at 22:17

answered Jul 31 '18 at 22:09

Brittany Hicks

34
4

`community_program` belongs to a `program_name` so I don't think I can create the scope you have described above. – anonn023432 Jul 31 '18 at 23:19

Rails find all records except already second level associated

4 Answers4