Rails has_many through with where condition

Question

I have the following associations in my Survey model:

has_many :survey_group_lists, -> { order 'sequence ASC, group_id ASC' }
has_many :groups, through: :survey_group_lists

I want to add where cluase to :groups association so it will return only active groups. I've tried something like this:

has_many :groups, -> { where(active: true) }, through: :survey_group_lists

but it returns me an error:

ActiveRecord::StatementInvalid: PG::UndefinedTable: ERROR:  missing FROM-clause entry for table "slide_groups"

What I'm doing wrong?

Edit: I'm using Rails 5.

Ultrasound

Did you find an answer to this? We're having the exact same problem over here, and still looking for the solution. – Conlen

Answer 1

has_many :groups, -> {where('groups.active' => true)}, through: :survey_group_lists

This is correct.

Note that the lambda has to be the second argument, otherwise you will see syntax error, unexpected '\n', expecting => .

Stellarator

Answer 2

try this:

has_many :groups, through: :survey_group_lists, -> { where(groups: {active: true}) }

Ripsaw

This returns syntax error: syntax error, unexpected '\n', expecting => – Southernmost

new line? where did it come from? – Ripsaw

it should be where('groups.active = ?', true) – Kuebbing

ohh, thanx @AndreyDeineko, try this and let us know – Ripsaw

still '\n' u mean? strange.. are you sure it is on this line, I doubt it is somewhere else in your code.. – Ripsaw

has_many :groups, through: :survey_group_lists, -> { where(groups: { active: true } ) } – Bombsight

The way to get this to work is to put the scope as the second argument. So change the answer to: has_many :groups, -> { where('groups.active' => true) }, through: :survey_group_lists – Owing

@MateuszUrbański does the solution provided above works for you? If yes, I'll update the answer.. :) – Ripsaw

@KevinSylvestre it works, I have used it multiple times..any error you receive? – Ripsaw

@Md.FarhanMemon unfortunately doesn't work for preload or include (not sure if you saw that in my original comment). It'll warn ActiveRecord::StatementInvalid: PG::UndefinedTable: ERROR: missing FROM-clause entry for table. It is fine for accessing otherwise. – Ejaculate

@KevinSylvestre if that's the case, can you confirm whether the asscoiation is working or not? like for above case Model.last.groups? – Ripsaw

Yup - that's fine. Like I mentioned, it is doesn't work for both preload and include. – Ejaculate

Answer 3

It should be:

has_many :groups, through: :survey_group_lists, -> { where(groups: { active: true } ) }

Karakorum

Answer 4

Try this one

has_many :survey_group_lists, -> { order 'sequence ASC, group_id ASC' }
has_many :active_survey_groups, -> { where active: true }, class_name: 'survey_group_lists'
has_many :groups, :through => :active_survey_groups

Sternick