Using Where on association's attributes condition

I have a User model which has a languages attribute as an array (postgres)

A User has_many :documents and a document belongs_to :user

I want to find all document that are written by users knows English and French

@langs = ["English", "French"]

Document.joins(:user).where(user.languages & @langs != nil )

This doesn't work.

What is the correct way to do this?

Schema for languages

t.string   "languages",              default: [],                                 array: true

Upvotes: 1

Answers (4)

Reputation: 3435

Try this:

Document.joins(:user).where("user.languages @> ARRAY[?]::varchar[]", @langs)

This should work I tried on my models with the similar structure.

Upvotes: 1

Reputation: 4279

Perhaps you need a "contains" operation on your array in the database:

Document.joins(:user).where("user.languages @> ARRAY[?]", @langs)

Upvotes: 1

Reputation: 456

A complete combination with @alex's answer will be:

@langs = ['English', 'French']

Document.joins(:user).where('user.languages in (?)', @langs)

This will generate Document object(s) that match(es) with the criteria :)

Upvotes: 0

Reputation: 3742

User.where('languages in (?)', @langs).map(&:documents).flatten.uniq

Upvotes: 0