Rails - has_many :through find records which have no association

Question

I have three models

class Person < ActiveRecord::Base
  has_many :assignments
  has_many :projects, through: :assignments
end

class Project < ActiveRecord::Base
  has_many :assignments
  has_many :people, through: :assignments
end

class Assignment < ActiveRecord::Base
  belongs_to :person
  belongs_to :project
end

I'm using a attribute called 'kind' on the Assignment model to determine how the person is associated with the property. It's a string field and possible values include: 'supervisor', 'worker', 'inspector'.

I've added the attribute to the Assignment model rather than the Person model because in some scenarios its possible for a person to be a worker on one project, and a supervisor on another at the same time.

Its important to note, that when created a supervisor is automatically assigned. Therefore all projects will have at least one assignment already.

What I'm wondering is this:

How do I query all projects which have no workers assigned? This would be a project which has no assignments which have 'worker' in the kind column.

score 1 · Answer 1 · answered Dec 31 '15 at 18:50

To be efficient you could use a counter_cache column.

class Assignment < ActiveRecord::Base
  belongs_to :person
  belongs_to :project, counter_cache: true
end

Project.where(assigments_count: 0)

Check the counter_cache part in: Rails guides And note that you will need to reset counter cache column on already created models.

score 1 · Accepted Answer · answered Dec 31 '15 at 19:27

1

You can use ActiveRecord's joins query method to accomplish this.

Project.joins(:assignments).where('assignments.kind <> ?', 'worker')

This will result in the following query

SELECT "projects".* FROM "projects" INNER JOIN "assignments" ON "assignments"."project_id" = "projects"."id" WHERE (assignments.kind <> 'worker')

Hope this helps. Cheers!

answered Dec 31 '15 at 19:27

Bart Jedrocha

11,450
5
43
53

thanks for this great answer. Interestingly "<>" didn't function as desired, whereas "!=" did. I changed it to: `Project.joins(:assignments).where('assignments.kind != ?', 'worker')` – greyoxide Dec 31 '15 at 20:07
No problem. That's interesting about `<>`, what DB are you using? – Bart Jedrocha Dec 31 '15 at 20:08
Odd indeed since in Postgres `<>` and `!=` are the same. [Reference](http://www.postgresql.org/docs/9.1/static/functions-comparison.html) – Bart Jedrocha Dec 31 '15 at 21:35

score 0 · Answer 3 · answered Dec 31 '15 at 18:58

0

Or just use SQL if you want to avoid extra columns and db updates SQL - find records from one table which don't exist in another + rails raw sql example

answered Dec 31 '15 at 18:58

bbozo

7,075
3
30
56

Rails - has_many :through find records which have no association

3 Answers3