2

User and Organization have a many-to-many association through Relationship.

One of the variables in the Relationship model is a boolean called member.
A user can occur in the Relationship model multiple times as a user can be related to multiple organizations.

How can I select a random instance from all the users that 1) don't have a relationship with any organization (so don't occur in the Relationship model) plus 2) those users that do have a relationship with an organization but for whom member = false for all their relationships with organizations?

I was thinking of something like:

user = User.where( relationship_id = nil || ??? ).offset(rand(0..100)).first
Marty
  • 2,132
  • 4
  • 21
  • 47
  • Please put the code of the relationships so we can better understand your particular case. – Gonzalo Robaina Jul 31 '15 at 15:03
  • Thanks, I've added the code to the original post. – Marty Jul 31 '15 at 15:11
  • If a user had a single Relationship with an organisation, and that had member = true, are then in or out of this selection? – Max Williams Jul 31 '15 at 15:11
  • @Max, then they are out of the selection (either have no relationship at all, or for all relationships have `member = false`). – Marty Jul 31 '15 at 15:11
  • This smells like the sort of instance where if you explained what you actually want to do, in english not code, then it might become apparent that you don't even need to do the thing you're asking about. What do you actually want to get, in a common sense sort of explanation? – Max Williams Jul 31 '15 at 15:12
  • When seeding, I want to give certain rights to a random selection of users who are not a member of any organization. – Marty Jul 31 '15 at 15:17

5 Answers5

2

I will do this

  1. User.joins("LEFT JOIN relationships ON relationships.user_id = users.id").where('relationships.user_id IS NULL').offset(rand(0..100)).first

  2. Something like:

    member_ids = Relationship.where(member: true).pluck(:user_id).uniq
users = User.where.not(id: member_ids) # or User.where('id NOT in (?)', member_ids) on Rails < 4
Andrea Salicetti
  • 2,423
  • 24
  • 37
  • The first one ignores the not a member of any context e.g. (member = false on all relationships) and also could be simplified with `includes(:relationships)` rather than the raw SQL join. The second answer is how I would handle this . – engineersmnky Jul 31 '15 at 16:20
  • You actually can't use `includes`, because then condition on included relationship won't work: http://apidock.com/rails/v4.2.1/ActiveRecord/QueryMethods/includes (see 'conditions' section). You could add some boilerplate ('reference' call) to make it work, but I consider that using 'joins' in the first place is more concise. – EugZol Jul 31 '15 at 16:40
1

So, what you actually want is users who aren't a member of any organisations, where "being a member" means "having a relationship join record where member = true". This is a much simpler concept than the conditions you specified.

In that case:

  • get distinct user_id from relationships where member = true
  • get users whose id IS NOT in this list

eg

member_ids = Relationship.where(member: true).distinct.pluck(:user_id)
@users = User.where("id not in (?)", member_ids).all
Max Williams
  • 32,435
  • 31
  • 130
  • 197
  • Thanks Max, this seems as it should do what I'm looking. I fail to test it because seeding fails. If you might also have an idea why that is, I'd love to hear. – Marty Jul 31 '15 at 15:29
  • I would do a different question for that, since it's a seperate problem. Generally, though, if you give people zero information other than "it fails" they won't be able to diagnose your problem ;-) – Max Williams Jul 31 '15 at 15:34
  • Okay, will do (once I can post another question). Once I get the seeding to work will report back if the code works. I'll roll back this post to remove the seeding issue. – Marty Jul 31 '15 at 15:42
  • Keep in mind that this solution will require database to send IDs of every Relationship record to your ruby code. And then your ruby code will have to send it back again. It's probably a better idea to make database filter ids by itself ;) – EugZol Jul 31 '15 at 15:44
  • Don't use SQL code inside where function, because it is susceptible of SQL Injection. Instead use `@users = User.where.not(id: member_ids).all`. Look at @Andrea Salicetti answer, it's what you want. – Miguel Cunha Jul 31 '15 at 16:09
  • 1
    In `User.where("id not in (?)", member_ids)`, rails will sanitize member_ids. Also, because i'm building this myself, rather than just shoving some value from `param` in there, sql injection isn't even an issue. – Max Williams Jul 31 '15 at 16:12
  • Similar to @AndreaSalicetti's only it is completely backwards compatible. Distinction is not needed as SQL will handle this fine e.g. `SELECT users.* FROM users WHERE id NOT IN(1,1,1);` will still only return 1 result. Although I don't feel it would have any overall performance impact either way. – engineersmnky Jul 31 '15 at 16:35
  • Hey @MaxWilliams! Is there any progress with the issue? :) – EugZol Aug 11 '15 at 10:42
  • @EugZol i don't know which issue you are referring to. This question has an answer marked as correct and is therefore concluded as far as i'm concerned. – Max Williams Aug 11 '15 at 11:29
0

For getting Users with no Organization:

User.where.not(id: Relationship.pluck(:user_id).uniq)

The other query doesn't seem like something you could be doing without some logic to compare the total Relationships for a User against those where member = false.

Gonzalo Robaina
  • 367
  • 3
  • 15
0

There is now a Where Exists gem which you can use. (Full disclosure: I've created that gem recently.)

# This will select all users, for which there are not relationships,
# or there is a relationship, but its 'member' attributes is false.
#
# Just what you've asked for.
users_to_select = User.where_not_exists(:relationships, member: true)

# 'offset' and 'count' will work as usual after 'where_not_exists'
random_user = users_to_select.offset(rand(users_to_select.count)).first
EugZol
  • 6,476
  • 22
  • 41
-1

Well, using ActiveRecord doesn't presume that you are prohibited to write SQL code. Actually, you are required to do so in many cases. Your case seems to be one of these (you can't do 'OR' query with ActiveRecord, and you there is no syntax sugar for SQL 'WHERE EXISTS').

So I suggest you do something like:

free_users = User.where("NOT EXISTS (SELECT 1 FROM relationships WHERE user_id = users.id) OR NOT EXISTS (SELECT 1 FROM relationships WHERE user_id = users.id AND member = FALSE)")
random_user = User.find(free_users.ids.sample)

More info on SQL "EXISTS":

http://www.techonthenet.com/sql/exists.php

UPD. Posted another answer which utilizes where_exists gem.

EugZol
  • 6,476
  • 22
  • 41
  • User doesn't have relationship_id – Gonzalo Robaina Jul 31 '15 at 15:23
  • @MiguelCunha And why exactly shouldn't I? – EugZol Jul 31 '15 at 15:30
  • 1
    But I don't have SQL injection in provided example, and neither should you have it in your SQL in where's. If you need to shove some parameters into raw SQL, use placeholders: http://thepugautomatic.com/2013/01/named-placeholders-in-active-record-queries/ (first link from Google). Rails will auto-escape them. Using SQL fragments in Rails is mainstream practice, you shouldn't fear it. Actually, you are very limited being stuck with only simplest methods that Ruby interface gives you. – EugZol Jul 31 '15 at 16:17
  • @MiguelCunha where could this method possibly be injected? There is no user input required. Also when using the `?` placeholder as in some of the other answers rails sanitizes the values appropriately as if they were passed in as a Hash. – engineersmnky Jul 31 '15 at 16:30
  • Look at @Andrea Salicetti answer. Also [this website is very interesting about injection in rails](http://rails-sqli.org/). You should use Rails functions wherever possible. I know that there are some limitations, but you can use additional libraries for that or you can (and should) rethink the app conceptual model / architecture. – Miguel Cunha Jul 31 '15 at 16:39
  • @MiguelCunha His answer gives more concise code, I give him that. But it will transfer ids of every relationship record twice: first to rails and then back again to DB engine. In most cases such thing should be avoided. Your link is irrelevant, because it presents cases where placeholders (named or unnamed `?`) ARE NOT used. – EugZol Jul 31 '15 at 16:44