Set a query filter

rubyonrails-talk
1

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…

2

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

  skills_you_want_to_display = Skill.all - user.skills

3

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

4

I just did :slight_smile:

5

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 Hassan Schroeder | about.me 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

6

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!

7

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.

8

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