Set a query filter

Hallo.
I have a user and a skill table joined in a SkillUser table. (has many through)
Now I try to get all Skills listed which are not already in the join table for user 1

In other words: user 1 has entries in the SkillUser table for skill 1 and 2. And I want to get skill 3…to …in the result, but not 1 or 2.

I have tried:

skills = Skill.sort.includes(:skill_users).where(‘skill_users.user_id NOT IN (?)’, @user.id)

But then I only get all the skills listed for all other users already listed in the SkillUser table.

Somehow I thing about a filter like
skill = Skill.sort
skill.filter(substract skills for user x which are already in SkillUser)

Any idea? Thanks for help…

Stop thinking in terms of tables and think about your models, e.g.

  skills_you_want_to_display = Skill.all - user.skills

Hi Hassan…

I have a user and a skill table joined in a SkillUser table. (has many
through)
Now I try to get all Skills listed which are not already in the join table
for user 1

Stop thinking in terms of tables and think about your models, e.g.

skills_you_want_to_display = Skill.all - user.skills

can you give me a practical sample…thanks


Hassan Schroeder ------------------------ hassan.schroeder@gmail.com
http://about.me/hassanschroeder
twitter: @hassan


You received this message because you are subscribed to the Google Groups “Ruby on Rails: Talk” group.
To post to this group, send email to rubyonrails-talk@googlegroups.com.
To unsubscribe from this group, send email to rubyonrails-talk+unsubscribe@googlegroups.com.
For more options, visit https://groups.google.com/groups/opt_out.

Werner Laude

webagentur.laude@gmail.com

I just did :slight_smile:

can you give me a practical sample..thanks

I just did :slight_smile:

This idea

Skill.all - user.skills

is totally new to me..
I will try tonight..ned some air now
Thanks so far

--
Hassan Schroeder ------------------------ hassan.schroeder@gmail.com
http://about.me/hassanschroeder
twitter: @hassan

--
You received this message because you are subscribed to the Google Groups "Ruby on Rails: Talk" group.
To post to this group, send email to rubyonrails-talk@googlegroups.com.
To unsubscribe from this group, send email to rubyonrails-talk+unsubscribe@googlegroups.com.
For more options, visit https://groups.google.com/groups/opt_out.

Werner Laude
webagentur.laude@gmail.com

Time to open up irb or a rails console and play around, e.g.

1.9.3p194 :014 > [1,2,3,4,5] - [1,2,3]
=> [4, 5]

Have fun!

I won’t generally go with that approach:

  • Skill.all can get big

  • You’re doing 2 queries by default plus the array subtraction

It’s better to just use a scope or a class method on Skill or some service class in my opinion but for brevity nothing tops that. The results would (should) be cached anyway.

I don't think the two-queries part can be avoided. If Skill.all is
big, maybe something like:

  Skill.where("skills.id NOT IN ?", user.skills.map(&:id))

Whatcha think?

-Dave